Menu
Menu
excel dashboards
The Essential Library Of Dashboard Formulas For Busy Professionals
It Seems Incredible That You Can Immediately Know The Secret Complex Formulas To Marry Your Data And Create Interactive And Dynamic Dashboards And Reports, Without Figuring Out Nor Remembering The Syntax!
"Are You Willing To Follow 50+ Dashboard Formula Templates To Pull Almost Any Answer From Your Excel Tables?"
This Could Be You!
The book is my ”lookup bible."
In my work with data tables, If the table was not in the right format to fit a Vlookup, I would arrange the data so that it would work.
After following your templates I am much more comfortable with a number of ways to lookup values from a table. The book is my ”lookup bible”.
Harold Grady
Analyst Professional, Boston
Wednesday, 6 PM
From: John Franco
Dear professional,
If you don’t have at your fingertips the many options available to extract data and would like to discover how to extract information from Excel tables, to answer unusual end-users questions and to create customized reports for you and for your manager, I can show you exactly how to do it quickly and easily.
In fact, I am going to reveal more done-for-you lookup formulas than you will ever need.
Listen closely now because finding and presenting information from tables is something I do extremely well. I have been successfully manipulating Excel Tables with hundreds, thousands, even hundreds of thousands of rows, multiple columns, and with multiple and complex criteria for over 10 years…
I started with the same problems you are having today, and I know what it feels like to be pulling my hair out for failing to adapt the existing formulas I knew, (VLOOKUP, IF, SUMIF, COUNTIF, Array Formulas, etc.) and not knowing the direct Lookup formulas that I knew existed or should exist.
That’s not all, These things really scared me…
- Wanting to be able to tell Excel that if certain columns match a given complex condition, then summarize these lines and give me a sum/count/vlookup in a different column or cell
- Having multiple lines which all pertain to one record and needing to be able to summarize them into one line, while they match a multiple column criteria
- Remembering the syntax when you need to start from scratch
- Getting rid of unwanted #N/A errors
- Implementing a VLOOKUP unsuccessfully and not having a clue about where to look at for a solution
- Ignoring how to prepare your data for successful lookups
- Having difficulty in debugging long and complex formulas
- Don’t know where to begin
And that’s just for starters!
Being fond of my hair, (it hurts when I pull it) I reached the point in which I said to myself…
“Self, there has got to be a way to do this,” but I just can’t figure out what it might be. Even after having looked through Excel Help, I could NOT seem to figure out which functions I needed to use, to pull out the answers I wanted…
After years of being “blank” in front of intimidating Excel tables, visiting Excel forums and blogs, doing research, etc. I discovered the direct Lookup, Array Formulas and other advanced formulas and techniques you need to translate the “end-users” unusual questions into answers for your reports. It changed my life and career dramatically!
Do you understand what this means?
Once I discovered this, I started to please my boss and customers with the reports they requested…. in a big way!
Once I discovered it, I wrote a complex Lookup formula in just minutes, and the report was configured in front of me.
I even started to massage the data for my own consumption in ways I could not do before. I started to make sense of the data and gain knowledge to make better decisions. Suddenly, I knew more than all my peers about our company’s figures and trends.
Generating further “meaningful information” from Excel tables was no longer a time-consuming process
I no longer feared my boss when he came with new and unusual reporting requests
And check this out…
I was soon considered for a promotion, and my career advanced!
But wait – there’s more
Now…I am helping hundreds of data professionals… of all experience levels with the same flat-table-conversation problems like yours; they are aspiring Data Analysts, Finance Assistants, Data Analysts, Financial Experts, HR Department Heads, and OTHER professionals, SOME OF whose clients include MANY of the best-known companies in the world, such as American Express, General Electric, BMW, Merrill Lynch, Kaiser Permamente, Honda, and several others. And many other small to mid companies, ranging from consulting firms to Internet providers and hotels.
For example…
What are others saying about The Essential Library Of Dashboard Formulas For Busy Professionals?
"Your book has already helped me to create a spreadsheet for a client that uses lookups extensively"
Just so you know, your book has already helped me to create a spreadsheet for a client that uses lookups extensively.
The clarity of explanations enabled me to quickly and easily understand the structure of a lookup and how to use them effectively. Until now, I haven’t needed them as VBA has been used to do what I needed. The formulas will be simpler for the client to maintain going forward. THANK YOU!
Jeff Jones
Excel/Access/VBA Expert at ExcelandAccess.com Helping Fortune 500 & SME organizations, Federal, State & Local agencies and Public & Private institutions, Raleigh-Durham, North Carolina
"Most industry books spend way too little time on VLOOKUP and all the DATA functions"
"This book is extremely helpful and insightful. Well worth the read.
I have to say the most refreshing thing about this is that most industry books spend way too little time on VLOOKUP and all the DATA functions...
You would think the world went Pivot Table crazy"
Kevin Fitzpatrick
HR Compensation & Business Analysis Manager at DTCC, New York City
"The book can teach you some really awesome tricks and help you become better at writing lookup formulas"
"I recommend the "How to Extract Information from Excel Tables" book for anyone working on Excel to manage and report data. The book can teach you some really awesome tricks and help you become better at writing lookup formulas.
That, my dear reader is the easiest way to impress everyone at work and get things done."
Chandoo
Excel author, Chandoo.org, India
Your book is my ”lookup bible"
"I view myself as an expert when it comes to Excel and using formulas, at least I thought so until I read your book. From a Vlookup perspective, I was very comfortable but your book goes beyond the basic Vlookup.
In my work with data tables, If the table was not in the right format to fit a Vlookup, I would arrange the data so that it would work.
After reading your book I am much more comfortable with a number of ways to lookup values from a table. Your book is my ”lookup bible”.
The ease of use of the book is very helpful and very friendly to use especially the How To with "the needle and haystack"
Harold Grady
Analyst Professional, Boston
I don’t care if you’ve never written a lookup formula in your life.
I’ve put all this information together into the simplified program “How To Extract Information From Excel Tables – 50+ Dashboard Formula Templates That Will Pull Almost Any Answer From Your Data”, that you should check out right now…
You get 50+ dashboard formula templates that will show you how to pull out single items from tables using simple and complex criteria (using VLOOKUP, INDEX & MATCH, Array Formulas), how to pull out summarized values from tables (using COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS, Array Formulas), how to pull arrays using VLOOKUP, INDEX & MATCH, Array Formulas.
You will also find a new and simpler way of understanding the arguments so you don’t need to remember the syntax every time. You will also learn the fundamental principles of looking up.
Everything revealed in pdf format (137 pages).
What’s in it for you….
- Learn how to easily implement a VLOOKUP successfully
- Stop the hair pulling and headaches! Discover – like many others who used to swear by VLOOKUP, – it is NOT the only way
- No more time sucking searches in several places (forums, blogs, several books, etc) only to find superficial results on the lookup formulas topic. This is like no other book on the market! An in-depth EASY TO FOLLOW reference guide about Excel Lookup formulas for Report generation
- Learn how RIGHT NOW – to easily execute a successful Vlookup (free of unwanted #N/A errors) with a step by step proven checklist and expert “best practice” guidance
- Never again feel “in the dark” and having no ideas about how to get rid of #N/A errors
- Avoid being paralyzed by a lack of knowledge in all the ways to manipulate data. In this guide, you will quickly learn that there are so many methods Excel possess for manipulating data effectively
- Find the direct Lookup formulas to crunch multiple columns and multiple lines – FAST and EASY
- Fearlessly work with Excel lists having multiple and complex criteria
- Stop experimenting with formulas you are not familiar with, and achieve the exact data extraction you want…. right now!
- Quit going in circles, trying to adapt formulas that are not suitable for your multiple criteria purposes: VLOOKUP, COUNTIF, SUMIF
- Instantly become the master of the most powerful methods to perform a complex criteria or multiple column lookup by using: INDEX and MATCH, SUMPRODUCT, Array Formulas
- Say goodbye forever to lost opportunities to shine at work, because you are not able to massage or make sense of information that lies dormant in your Excel List
- Permanently stop your “end users” from complaining about the layout of the report being too complex because you will easily be able to provide only the data they need and request
- Consistently answer unusual “end users” questions, letting them know that the last occasion was no coincidence or fluke
- Satisfy diverse “end user” format preferences, by developing any “end user” layout needed
Become an Excel Power User!
Get inspired by this comprehensive gallery of dashboard templates and develop the confidence to build your own mega formulas. You don’t need to start from scratch.
These are the done-for-you templates you will find inside…
How to pull out a single item (summary)
- Template 1 – How to look up the Quantity of a Given Item Using COUNTIF
- Template 2 – How to LOOK UP the Quantity of a Given Item that Meets N Criteria Using COUNTIFS
- Template 3 – How to look up the Quantity of a Given Item that Meets N Criteria Using Array Formulas
- Template 4 – How to look up the Amount of a Given Item Using SUMIF
- Template 5 – How to look up the Amount of a Given Item that Meets N Criteria Using SUMIFS
- Template 6 – How to look up the Amount of a Given Item that Meets N Criteria Using SUMPRODUCT
- Template 7 – How to look up the Amount of a Given Item that Meets N Criteria Using Array Formulas
- Template 8 – How to look up the Average of a Given Item Using AVERAGEIF
- Template 9 – How to look up the Average of a Given Item that Meets N Criteria Using AVERAGEIFS
- Template 10 – How to look up the Average of a Given Item that Meets N Criteria Using Array Formulas
How to pull out a single item (picking)
- Template 11 – How to look up a text
- Template 12 – How to look up a Date
- Template 13 – How to look up a Number
- Template 14 – How to look up a Text that is not Located in a Left Index Column
- Template 15 – How to look up a Date that is not Located in a Left Index Column
- Template 16 – How to look up a Number that is not Located in a Left Index Column
- Template 17 – How to look up a Text that Contains “”
- Template 18 – How to look up a Text that Begins with “”
- Template 19 – How to look up a Text that Ends with “”
- Template 20 – How to look up a Text that Contains “” and is not Located in a Left Index Column
- Template 21 – How to look up a Text that Begins with “” and is not Located in a Left Index Column
- Template 22 – How to look up a Text that Ends with “” and is not Located in a Left Index Column
- Template 23 – How to look up a Case Sensitive Text Using an Array Formula
- Template 24 – How to look up a Case Sensitive Text Using SUMPRODUCT
- Template 25 – How to look up a Text and a Number Located in Two Index Columns Using a Helper Column
- Template 26 – How to look up a Text and a Number Located in Two Index Columns Using SUMPRODUCT
- Template 27 – How to LOOK UP a Text and a Number in Two Index Columns Using an Array Formula
- Template 28 – How to look up a Month Using a Helper Column
- Template 29 – How to look up a Month Using SUMPRODUCT
- Template 30 – How to look up a Month Using an Array Formula
- Template 31 – How to look up a Number with Decimal Places
- Template 32 – How to look up the Last Instance of a Value
- Template 33 – How to look up the Most Frequent Value
- Template 34 – How to look up a Value Based on Index Column and Index Row
- Template 35 – How to look up in Reverse Mode
- Template 36 – How to look up Multiple Index Column Criteria
- Template 37 – How to look up the Biggest Quantity
- Template 38 – How to look up the Newest Date-Time
- Template 39 – How to look up an Exact or Approximate Number (case 1)
- Template 40 – How to look up an Exact or Approximate Text
- Template 41 – How to look up an Exact or Approximate Number (case 2)
- Template 42 – How to look up an Exact or Approximate Date/Time
- Template 43 – How to look up Exact or Approximate Percentages
- Template 44 – How to look up an Exact or Lower/Greater Approximate Number
- Template 45 – How to Extract one item using simple criteria using GETPIVOTDATA
- Template 46 – How to Extract one item using multiple criteria using GETPIVOTDATA
How to pull out an array (picking one by one)
- Template 47 – How to extract matches from different columns
- Template 48 – How to extract multiple matches that meet a single criterion
Who else uses this?
"...I am processing the formulas on my spreadsheets where applicable"
"Thank you,
There are a number of the formulas that I am familiar with. Those that I am not familiar with I am processing on my spreadsheets where applicable.
So slow going, but I want to make sure I understand the concepts."
Shawn B.
Assistant manager pharma services, New Zealand
"Anybody who has anything to do with data lookup should definitely read this book"
"I work in the Human Resources industry and data lookup and report generation is something that we need to do day in and day out, prior to reading this book I thought VLOOKUP was the only way and used to swear by the formula.
This book was truly enlightening and got to know so many ways to do an easy and effective lookup.
Still exploring the application of the lessons learnt from "The Essential Library Of Dashboard Formulas For Busy Professionals"
Anybody who has anything to do with data lookup should definitely read this book.
Thanks John"
Karthik G
HR Manager at Mascon Global Limited, India
What the Master Of Megaformulas Blueprint will do for you?
What else is in it for you?
- Stop sending so many Pivot Tables to old-fashioned users and Excel veterans, who usually hate PTs because they don’t know how to use them and it takes them too long to figure out
- Quickly understand the many useful functions yet unknown by many “Excel Experts”
- Always have options when you are not “comfortable” with one lookup method in particular. This guide offers you many very useful methods that will now, always be available
- Stop fighting your way through hard to understand technical guides full of “Geek Speak”, that are not written or formatted in a “User Friendly” way. This guide is not only VERY helpful, but EASY to use
- Instantly gain a deep understanding of the Lookup Formula arguments, making them much more manageable
- Stop racking your brain, wasting precious time in forums, and listening to “wanna be” experts to get the formulas you want
- Silence the complaints from your manager and customers about not being able to change the inputs in the reports and see the results immediately
- Prepare the reports exactly as your “end users” requested, with the same layout and figures, and no more
- Instantly have the expertise to know exactly how these complex lookup formulas work, so you can maintain them, even months after completing the work
- Kill the frustrations with complex computations, like multiple column and multiple criteria, that you can neither find a direct formula for, or don’t know how to apply your knowledge of the formulas you do have, to them
- No Worries about listening to a manager, and easily translating what she says into data fields
- Stop wasting your analysis time, by developing error-proof Lookup Formulas. You will do this by preparing the backend list to be queried properly, and by following clear guidelines at the frontend
- Your boss will trust your reports since they will receive legitimate and professional messages (“Items not found”, “Missing”, etc) instead of unexpected #N/As messages
- Never again struggle with a lack of resources (only Vlookup) when you face “growing-in-complexity” lookup computations
Now you will feel comfortable and confident doing Lookups every day, and have the energy to analyze data. You will start sending eye-opening and highly functional reports to the busy professionals and decision makers who request them
As it turns out…
How much should you invest in this megaformula building blueprint?
Extracting information from tables is not brain surgery. You can figure it out on your own. Eventually and if you are willing to invest time and effort doing trial and error trying to extract information from big tables, dealing with syntax issues, etc. Or reading books, forums, and blogs and synthesizing that information.
Or you can take advantage of the 10+ years John’s already invested in hard work dealing with extracting information from all kinds of Excel Tables, researching and synthesizing his findings.
There is absolutely no sane reason why you should want to waste years of your own career attempting to cover this same ground.
It’s up to you.
If you order today, you can unleash the data analysis power of Excel and quickly analyze and present data for just…
USD 147
USD 67
If you attend an Excel seminar, you will not cover even the fraction of the techniques covered here, and you will pay $300+ for one day of training, and spend even more on hotel and travel expenses.
And seriously…
There’s a saying that goes like this: “if you think education is expensive, try ignorance.”
…just think, how much will it cost you to do nothing?..
With a small investment, the Excel megaformulas skill will pay you back again and again.
If you don’t learn how to write, edit and fix complex long formulas, you will never be a full-fledged Excel user.
You risk nothing
If within 30 days of purchase, you follow the dashboard formula templates, do the homework and cannot adapt them to your situation for extracting information from your tables, or if you simply didn’t like the material or my style, just ask a refund and you will get your money back, no questions asked.
I have been selling Excel infoproducts online since 2011 to thousands of professionals from around the world.
Just contact us (use the subject, ‘Refund’). I will take care of it.
What's different from other Excel Lookup formulas courses out there?
Most of the Excel lookup formulas courses focus exclusively on VLOOKUP and on memorizing the function syntax, and explain how to write a basic lookup function. The HEIET program will show you more than 50 done-for-you lookup formulas (from basic to advanced lookups).
The comprehensive lookup scenarios are organized in a way that you can match them with your situations and will help you to be absolutely confident with complex lookup formulas from you and others.
Other courses about formulas assume you know a lot of things. At the beginning of the program, I don’t take anything for granted that you should know. I explain it the way it is done to a kid.
Who is the HEIET blueprint for?
The MMF Blueprint is for intermediate or advanced Excel users who already have created basic/complex formulas and are looking for new ways to tweak their existing complex formulas for higher functionality, want to get more context to face complex formulas with confidence, or want to systematize their formulas knowledge and skills.
The MMF program is for professionals and consultants who want to systematize their knowledge to transform Excel from a normal spreadsheet program into a dynamic and powerful analytics tool.
Intermediate to advanced Excel users will get the most out of this program because you might use this HEIET course as a refresher or for covering some voids you might have. Also, you can use any of the 50 lookup formulas logic to get ideas or actual solutions to face real projects right now.
You can ask for a full refund if you find the course is not for you.
But if you are a beginner, you can take this lookup program as an eye-opener to the possibilities that lies ahead beyond the VLOOKUP and INDEX & MATCH formulas.
Also, you can ask for a full refund if you find the course is not for you.
You are covered by my 30-day money back guarantee.
What do you get when you enroll in the HEIET program?
After you enroll, you get instant access to:
15+ in-depth lessons
Concepts explained using annotated graphics, simple language and analogies everyone can understand
10+ Formulas workbooks
More than 10 practice Workbook files in xlsx format (the same ones used in the program)
Forum
Post your projects on the forums and get feedback from me and other members on your same journey. Also, hone your skills by helping others in their Excel guru journey
User and password
Email with a username and a password to access the private members' area
Megaformula building blueprint
Follow a proven blueprint that will get your complex formulas done
Email support
One year of email support
Lifetime access
Learn at your own pace at any time. No need to follow rigid schedules
Free updates
Get free access to updated and new lessons, bonuses, for one year
Access anytime, anywhere
Access the members' area on any device (Android, iOS, Windows, Mac).
What else do you get when you enroll in the HEIET program?
Practical bonus #1 - 50+ Excel lookup templates (workbooks)
If you order today, you will also get 50+ Excel templates files, with the exact content of the book so you can easily follow along and implement the concepts.
If you are the kind of person that need to see things in action to understand and get started, then here you will find joy in seeing all the dashboard formulas in use and being able to actually play with them.
excelfilesbonus
And If you are the kind of person who loves to get your hands dirty without the frustration of trial-and-error, these dashboard workbooks will be one of the best learning experiences for you because each workbook easily presents the dashboard and its source data side-by-side (not on different sheets), so you can easily explore the techniques without losing sight of the source that is being used.
This side-by-side design allows you to analyze every argument and input, change the arguments and inputs, and see results immediately. Just see below…
heit-template-screen
And avoid the extra confusion of dealing with big tables because these examples don’t contain thousands of rows or hundred of columns. and as you can see above. Every template uses data of just the right size so you see how these fundamental techniques work. This will make you develop your confidence instead of being intimidated by big tables.
Practical bonus #2 - The Lookup error-free checklist
This is a set of three checklists to troubleshoot VLOOKUPs and INDEX & MATCH formulas with ease.
Find a list that will show you how to avoid and debug common lookup errors, and how to identify the good errors and avoid pulling your hair when you are getting mistakes that are factual and not derived from poor syntax or data.
One of the most fascinating things about this program is that develops the mindset to write complex formulas in Excel!
Enroll now and learn once for all how to write, edit and fix megaformulas.
Thanks!
P.S Remember, No More frustrations and headaches trying to manipulate data, or returning to the office tomorrow and being asked by “end users”, for reports you won’t know how to do.
P.P.S Don’t let the errors keep draining your energy before the analysis starts. What would you do with your time if you wouldn’t be in a lookup trap anymore?
FAQ
How much time should I devote to learning?
To complete the MMF program, you should complete 20+ lessons. But you don’t need to study the entire program in one sitting.
You can read the lessons at your own pace and you can also skip the areas you are familiar with or that you want to study later.
No pressure teacher. No rigid schedules. Study at your own pace.
What payment methods do you accept?
All major credit cards including Visa, MasterCard, and American Express are accepted. We also accept PayPal, Amazon Pay, and Wire transfer.
When does the course start and finish?
The access to the course starts now and ends after one year! But it is a self-paced online course. You decide when you start and when you finish.
The course site remembers your progress, so you can pick up right where you left off on any device. That means you can study a few lessons at work, then continue right where you left off on your tablet or home computer.
You can access the course site from your work or home computer, tablet, smartphone or any other device with an internet connection.
How long do I have access to the course?
You have one year access to the course, support, and forum. If you want to renew your membership, you can do so at 75% off. You will receive an email prior expiration to let you know.
What Excel version do I need?
The program covers fundamentals that are common to all Excel versions.
Except for some version-specific functions, all the functions, formulas and techniques will work on any version of Excel for either Windows or Mac. I use Excel 2016 for Windows in my screen captures.
The bonus Excel functions finder (EFF) will show you the functions that are available on Excel 2016 and 2019 versions.
Can I access the lessons on my mobile device?
Yes, you can log in with your smartphone or tablet (Android or iOS) and read or download the material. If you want to manipulate the workbooks, you might need an Excel app installed.
Of course, you can also log in with your computer or laptop (Windows or Mac).
Excel 2007 Conditional Formatting – 4 Awesome Reasons I Love this Command
The new Excel 2007 Conditional Formatting features allow you to apply icons and bar color scales instead of only colors and formats to a cell or array.
So you will see your spreadsheets converted into more powerful visual dashboards!
And this is just the tip of the iceberg! Yes, Excel 2007 brings other enhancements to this command. I will show them to you here…
For those who have not heard about Excel conditional formatting, it is a command that allows you to format cells or arrays based on its values or on the values of other cells.
The new functionality means that you can to do these things even better…
- Being informed in real time
- Get clues when editing massive databases
- Answer questions visually
- Analyze data: find exceptions, find relationships, find trends, etc
- Presenting data
- And more…
I will show here the 4 things I love about the Excel 2007 Conditional Formatting command. You will love them too!
You will want to put your hands to work on this immediately…
1) I love Excel 2007 Conditional Formatting because it has a new friendly user interface
You can easily select the type of rule you want to apply on the “Select a Rule Type:” area (see graphic below); and then you can edit the rule parameters on another clear separated area called “Edit the Rule Description:”
I like the fact that all the rules are shown at once, this way you have a sense of the big picture of which rule would be better to apply for a given scenario (Format all cells based on their values, Format only cells that contain, Format only top or bottom ranked values, Format only values that are above or below average, Format only unique or duplicate values, Use a formula to determine which cells to format).
Give a look at the New Formatting Rule dialog box…
Additionally, with the new Conditional Formatting Rules Manager (see graphic below) you can do several things you could not do before. For example:
- Now you can see/edit the range that the rules apply to
- Now you can move up/down each rule so you change the order in which the rules apply (so simple now but a not so straightforward process in Excel 2003)
- That’s not all, you can choose which workbook rules to show: selection, sheet, etc (see “Show formatting rules for:” area at the top of the manager dialog box below). This way you can work on a limited set of rules that you want to analyze the order of application, etc
In summary, the usability of conditional formatting command increased.
You can easily navigate through rules to administer them: creating, changing, deleting, reordering, etc. If you have been using the conditional formatting command on Excel 2003 you know that these little enhancements make a great difference.
2) I love Excel 2007 Conditional Formatting because it has pre-built formatting options
Once you access the command on the new Excel 2007 Ribbon (Home>Styles>Conditional Formatting), you can pick the preset rules and apply formatting right away.
For example: on the graphic below, you can choose one of the 5 preset formatting rules: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets.
After you choose one of them, you can then easily specify preset format criteria. More below…
Once you choose the type of rule and enter the condition, you can select a rich preset color configuration for your cells (see graphic below).
These preset formatting options allow you to prepare common formatting schemes quickly and easily. So in a couple of clicks you can format your cells as desired.
When can’t you use the preset rules and formatting?
As you will always need to create new and complex rules, you should create such criteria from scratch using the New Formatting rule manager (Home>Styles>Conditional Formatting>New Rule) or the Conditional Formatting Rules Manager ((Home>Styles>Conditional Formatting>Manage rules).
You will also love the next feature…
3) I love Excel 2007 Conditional Formatting because it has more rules and you can now move them up and down and more…
Excel 2003 and users of “previous versions” conditional formatting are limited to only 3 conditions (rules); see graphic below. There’s no such limit in Microsoft Office Excel 2007; you may have as many conditional formats as you like. And you can administer them all efficiently using the Conditional Formatting Rules Manager (see graphic above).
Let’s illustrate this shortcoming with an example…Imagine you had four age description ranges you would like to slice and dice with formatting. <2 infants, 2 – 18 children, 19 – 65 adults, >65 years old. Sorry, you are not able to do it with Excel 2003 conditional formatting (only 3 conditions).
Additionally, you cannot easily change the order of the conditions. Now you can! Yes, using the up/down arrows provided on the Conditional Formatting Rules Manager (see “Conditional Formatting Rules Manager” graphic above)
Here is another example…
Imagine you enter in Excel 2003 the conditions in this order:
- 2 – 18 children
- 19 – 65 adults
And then you want to add the condition <2 infants at the beginning. You need to reenter everything. You are not able to change the order of the conditions.
The other major limitation of conditional formats in Excel 2003 and earlier versions is that you couldn’t have multiple conditions be true for the same cell.
In Excel 2007, you can control whether the conditional formatting stops or continues after Excel discovers that a specific condition applies to a cell.
Don’t miss the next loved feature…
4) I love Excel 2007 Conditional Formatting because it has 4 new rule types (conditions)
More rule types means you can represent more types of trends and data; this can lead you to making awesome presentations and gaining powerful insights from your data.
You will hear great comments from the people who use your spreadsheets. And you will see the meaningful formatting taking shape as you change values in your sheet.
There is no reason to not put this to good use after you know the capabilities of the type of rules that Excel 2007 brings:
- Format all cells based on their values (New)
- Format only cells that contain
- Format only top or bottom ranked values (New)
- Format only values that are above or below average (New)
- Format only unique or duplicate values (New)
- Use a formula to determine which cells to format
Now, let’s explore each one briefly and envision some practical applications…
Rule 1) Format all cells based on their values (new)
This type of rule formats several cells based against the same scale. This rule type allows you to find relationships in your data.
The graphic below shows three columns. Conditional formatting in column A highlights cells according to a two color scale, highlights cells according to a data bar scale in column C, and highlights cells according to a icon set scale in column E.
You can compare sales, heights, etc.
Now, let’s explore the format styles very briefly…
Format style: 2 color scale and 3 color scale
Applications
- Sales categories
- Dates
- Age descriptions
- College grades
- And more…
Format style: Data bar
Applications
- Compare heights of the tallest mountains
- Traffic accidents
- Population
- Sales
- Profit/Losses
- And more…
Format style: Icon set
Applications
- Quickly see revenue status and trends from one quarter to the next
- Compare different product criteria by using a rating scale
- Examine profit trends from month to month
- Identify regional sales below $1,000,000
Rule 2) Cells that contains
This type of rule is useful when you want to spot trends individually on cells. For example: highlight the cells that contain value greater than 10,000.
The graphic below shows two columns. Conditional formatting in column A highlights values = 5, and highlights blanks in column C.
Applications
- Identify specific numbers, dates, and text in a list of products
- Find blanks, non blanks
- Identify dates that occur within specific periods like: today, yesterday, etc.
- And more…
Rule 3) Top/bottom rank (new)
You can use it to spot the top performing products, top performing students, Pareto 20%, etc.
The graphic below shows two columns. Conditional formatting in column A highlights top 20%, and highlights bottom 20% in column C.
Applications
- What are the top performing products in sales
- Who are the top two students in the class
- And more…
Rule 4) Above/below average (new)
The graphic below shows three columns. Conditional formatting in column A highlights “above average” cells (average =5.5), highlights “below average” cells on column C and highlights the cells that belong to the “1 std deviation above” in column C.
Applications
- Identify top, bottom, and above average values in a marathon training log
- Identify top, bottom, and above average values in students’ scores
- Identify top, bottom, and above average values in sales
Rule 5) Duplicates (new)
The graphic below shows two columns. Conditional formatting in column A highlights duplicate cells and highlights unique cells in column C.
Applications
- Find duplicate rows in a list of customers
- Find unique entries in a list and filter them out
- And more…
Rule 6) Formula
By using a formula, you can create any condition you can imagine.
The graphic below highlights the column A cells when the corresponding cell in column B is greater than 50
Applications
- Format alternate rows in a range
- Compare data in a cell outside the conditionally formatted range of cells
- Format an entire row where more than one condition must be true
- Format an entire row if the row is a unique value
- Format cells that match a set of parameters specified in another group of cells
- Identify a dynamically changed number or text value in a list of products
Conclusion
Manipulating rules is an important task, now you have the functionality to become a conditional formatting star.
And as I said to you above, more rule types and more representation types mean you can present data in more ways, get the message delivered and gain more new insights from your data.
You will increase satisfaction and confidence at work.
What are you waiting for?
Copy Conditional Formatting – 5 Handy Ways to Do It
Why do people want to copy conditional formatting in Excel?
The main reason – I think – is that nobody wants to retype conditions. Yes, Conditional formatting conditions may take time to develop and tweak. If they are in some workbook or sheet you certainly don’t need to retype them all again.
Here are 5 handy ways to copy conditional formatting in your Excel sheets…
It is important that you know…Excel treats conditional formatting as standard format, so with a simple copy and paste you will inherit the conditional formatting criteria. This is not always the case since you may have content in the target cells or perhaps you don’t know where the conditional formats are.
The following tips will show you how to do it quickly and easily for those various scenarios…
1) Copy and paste
Do this when the destination cells are empty or you want to replace the content.
Yes. Only copy the cell that contains the conditional formatting and choose the destination cell or range and paste.
2) Copy and paste special
Do this when the destination cells are not empty.
Select the range>Copy>Select destination cells>Edit>Paste Special>Formats>Ok
On Excel 2007
Select the range>Copy>Select destination cells>Home>Clipboard>Paste>Paste special>Formats>Ok
The next tip is quicker than this one…
3) Use the Format Painter to Copy Conditional Formatting
I love this way of copying Excel conditional formatting. It is handy and you are accustomed to doing it with plain formatting.
Just select the cell or range with the conditional formatting you want to copy, and then pick the format painter (if you don’t know the location of the tool you are living under a rock!) and then click on the target cells. The conditional formatting will be inherited.
Tip: double click the painter so you can apply format to multiple target cells or ranges
As I said to you before, Excel treats conditional format the same way as plain format.
The following way of copying conditional formatting is not so know but still very powerful…
4) Extend list formats and formulas to Copy Conditional Formatting
It means that the format of the table will be automatically replicated downward as you add more entries to the list.
The graphic below illustrates the point…
The cell A7 doesn’t have any conditional format. If you have the extend list formats and formula option activated; then, the cell A7 will acquire the adjacent table formatting (color if cell < 30, in this example).
This option is activated by default. If not…
Activate this option this way:
On Excel 2003 go to Tools>Options>Edit>extend list formats and formulas
On Excel 2007 go to Office button>Excel Options>Advanced>Editing options>Extend data range formats and formulas
Then, the formatting is copied automatically as you enter data down in your lists.
5) Select the adjacent range and apply conditional formatting (Excel 2003)
You can select contiguous cells to the range that contains the conditional formatting, and select the conditional formatting dialog and click ok.
The format will be copied to the adjacent cells.
Conclusion:
Now that you know more than one way to copy conditional formatting, you won’t be out of ideas to do it. You will do it with confidence and speed.
Important reminders
- Sometimes the destination range does not contain the same values that trigger the conditional formatting format. This does not mean that the conditional formatting has not been copied. If the cell remains non-formatted, change the value and see that it works
- You can copy and paste conditional formatting between sheets and workbooks the same easy way
- If you don’t know where the cells with conditional format are, use Go To command this way: F5>Special>Select>Conditional formats>Ok
That’s all for now….
Please use these tips to develop and hone your Excel skills, advance your business and your career, and to add value to the world.
Excel Conditional Formatting – 5 Killer Reasons to Use It
What is Excel Conditional Formatting and why is using it so advantageous?
In a few words: ECF is assigning formats to cells if their values meet a given condition. For example: in the graphic below…if the balance is negative, the cells are colored…
It appears simple at first sight but once you start using it, you uncover many practical applications. And you will never be the same after you add this professional ingredient to your spreadsheets.
Here I will share with you some of the handy applications and benefits of using Excel Conditional Formatting command extensively…
Before we move forward…I want to share this thought with you: If Excel Conditional Formatting is so important then…Why don’t Excel users use it frequently? I really don’t know, I personally didn’t use it too much either until I discovered my productivity increased with a few applications.
I think we need to know some WHYs so you start using this awesome command right away.
And the more you use it, the more you will find creative uses for it.
Here are the reasons…
1) Excel conditional formatting keeps you informed in real time
You can create your own checks. As soon as they occur you will be notified by a change in cell formatting. Colors will get your attention and you will never miss a model feedback. This of course means you can make informed decisions and make them faster.
For example:
- You can get a red color each time you insert a duplicate
- You can create a check to alert you each time you enter a non-valid value
- You can get an orange color as your month spending reaches a preset limit
- And more…
Real time highlights means you get real time insights. You can make decisions right away instead of allowing the errors remain hidden or replicate in second instances that can lead to catastrophic consequences and delay important decisions.
That’s not all…Immediate feedback can help you to keep alert of underlying patterns taking shape!
When important decisions happen fast and accurately, it’s the type of thing that catapults careers and businesses into leaders of their field. When your work helps to make your company a rock star, or even to take a few solid steps in that direction, bosses have a tendency to offer you rewards and promotions.
Don’t miss the second great application…
2) Excel conditional formatting helps you edit massive databases
It is a very common task to edit data that does not comply with your requirements: duplicates, length of cell content, numbers stored as texts, etc.
This happens because Excel communicates with such diverse software packages and these programs don’t export the data as we would like. When working with thousands of lines of data, this can often be overwhelming.
You can highlight those problems while editing the raw data so you don’t over sight any error. And even better, you can combine the highlight with filters and sort commands to edit massive rows in seconds
Examples of conditional formatting on Excel:
- Highlight Duplicates and decide which ones to delete
- Highlight Unique entries and decide which one to keep
- Highlight invalid data so you can filter them and correct them
- Highlight specific text
- Highlight dates occurring in certain periods: today, last week, etc
- Highlight Blanks, No blanks, Errors, No errors
- And more…
Here’s more…
3) Excel conditional formatting helps you to answer questions visually
As a data analyst you play with a table to get specific answers. While you can create custom filter criteria or use lookup formulas or Pivot Tables, you can create conditional format criteria to show you where the answers are.
Examples of these questions may be:
- What are the trends in product preferences over the past two years?
- Who has sold more than $100,000 dollars this month?
- What is the overall age distribution of employees?
- Which products have greater than 10% revenue increases from month to month?
- Who are the highest performing and lowest performing students in the freshman class?
“The more you use it, the more you will find creative uses for it”.
4) Excel conditional formatting saves time
Since Excel conditional formatting is updated automatically, you can manipulate text on the go, you can change the underlying cells and see what’s going on, what’s still missing or needs to be added, etc
What I mean is that once you set the conditions that trigger the formats, you focus on editing, analyzing and making decisions accordingly without extra configuration.
And finally…
5) Excel conditional formatting helps you analyze data
If you put conditional formatting on key metrics that need to be measured and monitored against target values, then you can take a look at your dashboards and get a better understanding of patterns.
Conclusion
Additionally, my satisfaction is increased since I feel a kind of connection with my own models. End users also feel comfortable when they are notified by color rules.
No excuses not to start using Excel Conditional Formatting and let YOUR inner ‘Excel Rock Star’ loose today!