Excel Tips

http://chandoo.org/wp/2009/12/30/best-of-pointy-haired-dilbert-2009/

January 2009
The year 2009 has been pivotal in PHD’s life. It all started with a friendly email from Microsoft
on Jan 1st telling me that PHD become an MVP. I felt wonderful knowing that. Even though the
award meant little in terms of benefits, it is a great tribute to our little community and the passion
we share here.
Later in the first week we wrote a post on how to make combination charts in excel. The
discussion on combo charts continued throughout the year, so much that when I posted a holiday
greeting card at the end of the year, Santhosh, one of our regulars said that they card looked like
a combo chart.
Most importantly we have crossed the milestone of 2000 RSS subscribers in the Jan 2009. To
celebrate that I posted one hundred excel tips. That was fun (plus Jo hated me for sitting in front
of computer that long).
February
February is fun. I started off the month with an excel twitter client. Which became a wild hit on
internet (ok, not so wild, but few of the other blogs in excel community did mention it. Also, JP,
the rockstar VBA blogger at codeforoutlookandexcel made an add-in out of it)
Later in the month I got too excited to discover that you can use excel data filters to make a
dynamic chart. By far the cheapest and easiest way to make a dynamic chart. We continued the
discussion on dynamic charts for the rest of the year and posted several ways to make them.
March
I celebrated the one year anniversary of “conditional formatting rockstar” post by writing 5 more
posts on excel conditional formatting. The series started with conditional formatting basics and
went on to talk about how you can solve 4 most common problems using excel CF.
We also started writing about excel array formulas and continued that discussion off and on.
Array formulas area fun and easy to write (once you have the basics right).
April
This has been a dull month what with my transfer from India to Sweden and sudden lack of
internet connectivity. Despite all that we wrapped our first visualization contest on budget vs
actual charts and posted some really excellent charting alternatives to the familiar problem.
PHD is also featured on Lifehacker for the Excel Formulas Errors – How to handle them? post.
May
We have proposed “Tweetboards” as an alternative to traditional dashboards and generated good
bit of discussion in May. Later several readers emailed me their tweetboard implementations.
Slowly tweetboards are spreading in the wild
We also rounded up all the Excel 2007 Productivity Tips.
June
I have stared the Project Management using Excel series in this month with Project Management
Gantt Charts. The 6+1 posts soon became legendary and helped me launch the project
management templates. In total these posts had more than 200 comments, 150k page views in a
short time.
June also was the best month PHD’s history as the blog got featured again on Lifehacker and
Delicious home pages for the Excel Mouse Tricks post. Later that month we have rounded up all
the techniques you can use to convert excel files to pdfs.
July
We focused on charting more and had the 14 skills you must have for making better charts. I
have also written about the all too familiar sumif with multiple criteria problem and some
formula solutions for it.
Later that month my post on Using Excel Goal Seek and Finding how much you need for
retirement got mentioned in Lifehacker and fetched me a ton of new visitors.
August
Thanks to Aaron, who guest posted about excel waterfall charts in August. In august, I have
turned my attention towards the pivot tables and wrote Excel Pivot Tables Tutorial. I have been
playing with pivots off and on for a while and this post was my first serious attempt to explore
the features. Later I wrote more about them and I am planning to explore pivot tables further in
2010.
In august, we have also crossed the 5000 RSS subscriber mark and celebrated it with a huge
contest. Later that month I have wrapped up all the contest entries in the Excel Formulas – 29
tips post.
September
I have started the month with a discussion on Pareto Charts and how to make them in excel.
Later that month I wrote about Excel Data Tables features. Both of these posts attracted a lot of
discussion and helped me learn valuable new tricks in excel.
Later that month, on September 24th, I became a dad. My life has been the most wonderful and
beautiful ever since.
October
In October we wrapped up the project management series with a Project Status Dashboards using
Excel. Later that month I have launched the project management templates for excel product. I
met several new customers and started to believe that I can make a living out of this blog.
November
In November, we started our most ambitious visualization challenge ever with the Zoho Sales
Data Visualization challenge. We now have more than 30 excellent entries and I am waiting for
Jan4th when we announce the voting for winner.
Also I have posted about the sumproduct formula and reviewed excel 2010.
December
We started the month with a discussion on using drawing shapes along with charts to make better
dashboards. Later in the month I have written about making a quick thermo-meter chart and
posted alternatives to compare targets using charts.
Finally I have released the free 2010 calendar excel for you to download and print copies.
*************************************************************
Excel Basics – What are Combination Charts and How to
Make One?
Posted on January 5th, 2009 in Charts and Graphs , Learn Excel - 14 comments

This post is part of SpreadCheats series, read the rest of posts and cheat excel to become
productive.
A combination chart is when you combine two different charts to make one. A popular
example of combination charts is a line & bar graph combination. See below illustration:

Today we will learn how to create a simple combination chart.

1. For our combination chart, we will use the following data of Sales & Profits

2. First we will make a simple bar chart for the sales data. Just select the Sales
data table, go to insert > chart and specify type as “column chart” (this is the
 default selection btw). Once you are done, the chart looks like this:

3. Now, select the profits data, press ctrl+c to copy it to clip board, and select
the sales chart you have created above, and press ctrl+v to paste this data in
to the sales chart. Essentially we are adding one more series of data to the
sales chart.
4. Now the chart should look like below. Right click on the new series (profits)
and select “chart type”.

5. From the chart type dialog change the type of chart from “column”
to “line” (or whatever other type you fancy)
6. That is all, you have successfully created a combination chart in excel.
Make a Dynamic Chart using Data Filters
Posted on February 12th, 2009 in Charts and Graphs , Learn Excel - 14 comments

This post is part of our spreadcheats series. Please read other posts in this series to know how
you can cheat excel to become more productive at work.
Do you know that you can create dynamic charts in excel using data filters ? Yes, that is
right, we can use data filters as chart filters too. When you apply a data filter on a chart’s source
data, the chart is also filtered.
See this screencast to understand it: (if you cant see it click here)

This technique is much simpler than dynamic charts using drop-down lists and INDEX formula
idea presented earlier. All you need to do is,
• Create chart for all your data. Include sales, profits and expenses
• Now, apply data filters to the source data range (menu > data > filter > auto
filter, in excel 2007, use home ribbon > filter & sort button > filter)
• Select the type of data you want to use in the chart by applying a data filter
 • Bingo, you have a dynamic chart that can be controlled using data filter
settings.

Collapse, Expand Excel Charts using this hidden trick

Posted on January 12th, 2010 in Charts and Graphs - 9 comments

Do you know that you collapse or expand excel charts? Don’t believe me? Me neither. When I
first realized that we can collapse / expand charts without writing any macros or lengthy
formulas, I couldn’t wait to share it with all of you. This is such a simple yet powerful trick. See
it for yourself.

If you want to collapse / expand an excel chart like this, Just follow the below steps.
1. Place your data in rows
Place your data like this.
Make sure you have an empty column next to each series of data. You can also place your data in
columns instead of rows. Also summary row (in our case – yearly total) is added and calculated
using a formula.
2. Make charts and Position them in the extra column
Select data for each year and make one chart. Since the data is in rows, select a bar chart. Make
sure you position the charts in blank columns. Remove any chart axis, grid lines etc if you feel
like.
At this point our set up should look like this:

3. Now select the detail rows and Group them

Just select all the rows with detailed data (in our case, the monthly sale values) and group them.
To group rows, go to Data Ribbon > Group in Excel 2007+ or
Data Menu > Group and Outline > Group in Excel 2003. See the below to understand.
Group Data – Excel 2003 Group Data – Excel 2007+
4. Finally Adjust Chart Positions so that when you Group the Chart Collapses
This is the tricky part. Depending on excel version, you need to carefully adjust the chart’s size
and position (top, left) and data series gap so that when you press “collapse” button from
grouping area on left, the chart also collapses neatly.
This step is very straight forward in Excel 2007, but in Excel 2003 it takes some patience. Once
you finish it, the collapsible chart is ready.
Go ahead and show it off to your boss or colleague, just wow them.
Making a Dynamic Dashboard in Excel [Part 1 of 4]
Posted on March 16th, 2010 in Charts and Graphs , Featured , Learn Excel - 15
comments

This is a guest post by Myles Arnott from Clarity Consultancy Services – UK.
In this and next 3 posts, we will learn how to make a Dynamic Dashboard using Microsoft
Excel.
At the end of this tutorial, you will learn how easy it is to set up a dynamic dashboard using
excel formulas and simple VBA macros.

[Click here for large version of the image]

Introduction:
The dashboard also demonstrates the standard approach I use in all of my models which is to
incorporate three key sheets in addition to the data and analysis tabs.
These are:
• Home page
• Inputs (or drivers)
• Helpsheet
The dynamic dashboard can be downloaded here [mirror, ZIP Version]
The dashboard file works in Excel 2007+. Pls. enable macros to get it work.
The plan is to break this dashboard tutorial down into four parts over the next four weeks. If
further topics fall out as a result of discussions either Chandoo or I will pick them up and if
necessary post further parts.
• Part 1: Introduction & overview
• Part 2: Dynamic Charts in the Dashboard
• Part 3: VBA behind the Dynamic Dashboard a simple example
• Part 4: Pulling it all together
I would like to take a quick opportunity to give credit for some of the elements of functionality in
the model:
• Boxcharts – Chandoo [Link]
• Scrolling report – Chandoo [Link]
• Competitor analysis – Chandoo [Link]
• Use of camera tool – Chandoo [Link]
• In cell microcharts – Chandoo [Link]
• Helpsheet – John Walkenbach
Okay so lets get started with an overview:
What is the objective of the report?
The Dynamic Dashboard is intended to provide pertinent summary information to aid
management decision making. Combining a high level of flexibility within each report and then
allowing the user to choose which reports to include and where to position them allows an
enormous amount of flexibility over the message to be communicated.
What does this Dynamic Dashboard do?
The dynamic dashboard allows the user to select a report from the range of reports within
the model and decide where to position it on the page. The user can select “hide” to hide a
report that they do not want to see or select “view” to preview it prior to choosing its position.
 • Clicking on either the hyperlink name or the report image will take you to the
report.
• Each report is highly flexible allowing the user to cut the data in many ways
to show management the most pertinent information.

Overview of Dashboard Tabs:

Home Page
I always include a homepage in my models and often set an auto_open routine to select this as
the first page seen on opening. The Home page is designed to present the contents of the model
to the user and provide links to each page for easy navigation.

The Dynamic Dashboard

This is the main tab for pulling together the dashboard and will be covered in parts 3 and 4.

Inputs
This is the page for all validation lists and drivers.

Help Sheet
Once again a sheet that is in all of my models. This user form based help sheet provides the user
with a quick help function and complements the accompanying user notes. I find it helpful to lay
it out in tab order.

This is how the Help user form looks once opened. The user can either choose the topic from the
dropdown or by clicking next.

Chart 1 and 2 : Flexible pie charts

Dynamic pie charts with the option to select the KPI, period and product/salesperson to be
analyzed. These are covered in part 2.

Chart 3 & 4: Flexible line charts

Dynamic line charts with the option to select the KPI, period and product/salesperson to be
analyzed. These are also covered in part 2.

Chart 5: Box Chart

Details on how to create these box charts.

Chart 6: Scrolling Report of KPIs

Chandoo’s blog on how to create this scrolling report can be found here. Micro charts which is of
my favorite blogs from Chandoo are covered here.
Chart 7: Scrolling Comparison Chart
Details on how to create this scrolling chart.

Chart 8 : Executive Summary

A simple executive summary. Please see Chandoo’s article on a twitter board for an alternative
view.
So that was an overview of the model and its main tabs.
Excel Bullet Graphs
Posted on July 21st, 2008 in Analytics , Charts and Graphs , Featured , Learn Excel ,
hacks , ideas , technology - 17 comments

Bullet graphs provide an effective way to dashboard target vs.

actual performance data, the bread and butter of corporate
analytics.
Howmuchever effective they are, the sad truth is there is no one
easy way to do them in excel. I have prepared a short tutorial that
can make you a dashboard ninja without writing extensive
formulas or installing unknown add-ins. So get out your
shinobigatana and join me in a fresh excel sheet arena.
Before we create our first bullet graph, let us spend a few
moments understanding these graphs. Stephen Few proposed
bullet graphs as way to provide crisp view of “target vs. actual
performance” numbers. Shown below is a sample bullet graph and how you would read it.

Read up more on this at PTS blog and on a Gauge chart that actually works.

Let us create your first bullet graph

Click here to download bullet-graph template excel sheet so that you can see while reading
Our technique of involves conditional formatting and simple formulas applied to a cell grid. Just
follow these 4 easy steps:
Step 1: Prepare your data for charting
Since we are going to plot bullet graphs on a cell grid, we first
need to normalize our data. I have chosen to plot each bullet graph
on 20 cells in a row as shown in the raw grid shown to the right:
Assuming we have fictitious sales data like this:
You can normalize YTD sales figures using a simple formula like this : ROUND(YTD-
sales/target*20,0)
Now that we have our data steaming hot, lets brew the graphs
Step 2: Lets make the raw grid formatted based on data
Now we will take the raw 20 cell grid in each row and conditionally format these cells so that we
have background of the bullet graph drawn on them.
For eg. If the normalized sales data for Bad range is 7 and for OK Range is 15 then,
We will highlight first 7 cells lighter shade of gray, next 8 cells gray and last 5 cells with darker
shade of gray.
I have shown the conditional formatting applied to these cells below:

When we are done, a sample row looks like this:

We have our cell grids ready now, lets shoot some bullets.
Step 3: Plot bullets on our graph canvas
Our final step involves print a bullet symbol (either – or + or | ) in each cell depending on one of
the following conditions:
1. If the cell position (1,2,3 … 20) is equal to Year ago value and cell position is less than YTD
value print a + symbol
2. If the cell position is equal to Year ago value and cell position is more than YTD value print a
| symbol
3. If the cell position is less than YTD value print a –
4. Else print a blank
See the formula below:

Download the excel template for bullet graphs to understand this formula better
Step 4: Show off your bullet graphs, awe your boss or colleagues, bask in your
Ninja glory
Unfortunately, I cannot tell you how to do this. I can only teach you to be a Ninja, but you have
to be one to charm people with your tactics.
Shown below is another variation you can try. Also, you can experiment with the symbols
printed (instead of + – | you can try other ASCII characters, for more download the excel sheet
containing bullet graph templates)
Also try: Partition charts, Incell Graphs and much more.
Us vs. Them – Compare Sales Performance using Charts &
Form Controls
Posted on March 12th, 2009 in Charts and Graphs , Featured , Learn Excel - 12
comments

One of the common uses of charts is to compare one value with another. For eg. our sales vs.
competitor sales.
Today we will learn a little trick to compare 1 value with another, especially when you have a
large set of values to compare. We will learn how to create a chart like this:

Prepare your data

By now you must have realized what the first step for most of our tutorials. It is always prepare
your data (or occasionally it is Have you signed up for PHD, but not today). Assuming you have
the data in the table format shown to the right, we will also create a 4×2 table to hold the 4 items
to be displayed in the chart. First item in the chart is “our sales”, remaining three are competitor
figures which will change based on scroll bar position.
We will comeback to how the other three items are computed. So dont worry about them at this
point.
Add a Scroll Bar Form Control
Now comes the fun part. Insert a scrollbar control in your workbook. If you are using excel 2003
and earlier, you can find it under view > toolbars > forms and select the scroll bar option and
draw it on your worksheet. For excel 2007 you can find it in developer ribbon tab (you need to
turn this on first from Office button > Excel Options)
Once you have added a scrollbar control, right click on it and select Format Control option. Here
in the “control” tab, adjust the settings of the scrollbar to something like this.

Make sure you have adjusted the minimum and maximum values based on the amount of data
you have. In our case we have 10 values to display and at any point we are displaying 3+1
values, so the maximum is 10+3-1 = 8 (why so? think…)
Also, mention a cell link where the scroll bar selection is updated. We will use this cell (F11) to
calculate the 3 values to be displayed.
Now, Write the Formulas to Display 3 Values
This is very simple step, especially if you know excel INDEX formula.
What is Index formula and how it works?
Index() is your way of telling excel to fetch a particular item in a list by its position (unlike
vlookup or match which lookup a matching item). For eg. INDEX(list, 10) returns the 10th value
in that list. INDEX works with both lists and tables, when used on tables, you should also specify
which column you want the value from. For eg. INDEX(table, 5,3) returns the 3rd element in 5th
row.
Ok, so how do we use it to fetch values for our “data shown” table?
Simple, we use a formula like =INDEX(names_list,F11) to fetch the first competitor name,
=INDEX(sales_list,F11) to fetch first competitor sales figures. For the other two, we can use
formula like.. =INDEX(names_list,F11+1), =INDEX(names_list,F11+2)
Finally We Make the Comparison Chart
We will insert a simple column chart based on “data shown” table. Adjust the formatting for first
column. And position the scroll bar beneath last 3 columns. And you are good to go.
Incell Bar charts – Revisited
Posted on July 15th, 2008 in Charts and Graphs , Learn Excel , hacks , ideas ,
technology - 18 comments

I can never get tired of in-cell charts,

whenever I get sometime, I try to
experiment something on them. Here is an
idea to design true incell column charts
without using any add-ins or installing
fonts. These charts can be fun to have on
your project report or annual news letter
or memos, they take as much time as
munching a handful of M&Ms.
Download excel chart containing incell
bar chart example
1. First we will create in-cell chart
using rept() the usual way
This is the simple step. For a sample data, we can
use rept() excel function to repetitively print a
symbol. Thus, by repetitively printing “|” (pipe)
we can create incell bar charts.
REPT() example is shown below:
=rept("|",10) will print ||||||||||
2. Next combine several incell charts to create a column chart
We can combine various incell charts using excel’s concatenate() function or & operator. Better
still you can use the concat() UDF. The trick here is we will delimit each chart with an ENTER
key code by using char(10) as the delimiter. When used along with “wrap text” cell format this
key code will force cell contents to a newline where the char code 10 is inserted.

3. Finally rotate the cell contents by 900 to make the charts vertical
Just select the cell contents, hit ALT+1, and set the alignment formats so that the text is
vertically aligned in the cell, and turn on wrap text.
That is all, you will now have an incell bar chart that is vertical like the one below:

Pretty cool eh?

Excel Conditional Formatting Basics
Posted on March 13th, 2009 in Excel Howtos , Learn Excel - 29 comments

Today is the first anniversary of Excel Conditional Formatting post (Don’t

worry, I am not going to make anniversary posts for all the 150 odd excel articles
here). This is the most popular post on PHD. The post has 100 comments and
bookmarked on delicious more than 700 times. It is truly a rock star post on PHD.
To celebrate the 1 year of teaching conditional formatting to you all, we have a series of posts,
the first of which is “What is excel conditional formatting & How to use it?”
What is excel conditional formatting ?
Conditional formatting is your way of telling excel to format all the cells that meet a criteria in a
certain way. For eg. you can use conditional formatting to change the font color of all cells with
negative values or change background color of cells with duplicate values.
Why use conditional formatting?
Of course, you can manually change the formats of cells that meet a criteria. But this a
cumbersome and repetitive process. Especially if you have large set of values or your values
change often. That is why we use conditional formatting. To automatically change formatting
when a cell meets certain criteria.
Few Examples of Conditional Formatting
Here are 3 examples of conditional formatting.

So How do I Apply Conditional Formatting?

This is very simple. First select the cells you want to format conditionally. Click on
menu > format > conditional formatting or the big conditional formatting button in
Excel 2007.
(we have used excel 2003 in this tutorial, but conditional formatting is similar in
excel 2007 with lots of additional features)
You will see a dialog like this:

There are 2 types of conditions:

• Cell value based conditions: These conditions are tested on the cell value
itself. So if you select a bunch of cells, and mention the condition as between
15 and 25, all the cells with values between 15 and 25 are formatted as you
specify.
• Formula based conditions: Sometimes you need more flexibility than a
few simple conditions. That is when formulas come handy. Conditional
Formatting Formulas are slightly complicated and can be difficult to learn or
use if you are new to excel. But they are very useful and intuitive and if you
use them once you get a hang of it.
What are the limitations of Conditional Formatting?
In earlier versions of Excel you can only define max. of 3 conditions. This is no longer true if
you are using Excel 2007 (read our review of excel 2007)
However, you can overcome the conditional formatting limitation using VBA macros (again, if
you are new to excel, you may want to wait few weeks before plunging in to VBA)
Also, you can only use conditional formatting with cells and not with other objects like charts.
Ok, Enough Theory, Time for your First Conditional Formatting
Go ahead, open a new workbook and try few conditional formats yourself. See how easy and
intuitive it is. Use it in your day to day work and impress your colleagues. Learn 5 impressive
tricks about conditional formatting.
Conditional Formatting in Excel – 5 Tips to make you a
Rockstar
Posted on March 13th, 2008 in All Time Hits , Analytics , Charts and Graphs ,
Featured , Learn Excel - 186 comments

Excel conditional formatting is a hidden and powerful gem

that when used well, can change the outlook of your project
report / sales budget / project plan or analytical outputs
from bunch of raw data in default fonts to something truly
professional and good looking. Better still, you dont even
need to be a guru or excel pro to achieve dramatic results.
All you need is some coffee and this post to learn some cool
conditional formatting tricks.
So you got your coffee mug? well, lets start!
The 5 tricks we are going to learn are,
1. Highlighting alternative rows / columns in tables
2. No-nonsense project plans / gantt charts
3. Extreme Incell graphs
4. Highlight mistakes, errors, omissions, repetitions
5. Create intuitive dashboards
If you are new to Excel Conditional Formatting, please read the Conditional Formatting
Basics article before proceeding.
I have created an excel sheet containing all these examples. Feel free to download the excel and
be a conditional formatting rock star
1. Highlighting alternative rows / columns in tables:

Often when you present data in a

large table it looks monotonous
and is difficult to read. This is
because your eyes start
interpreting the data as grid
instead of some important
numbers. To break this you try
highlighting or changing the
background color of alternative
rows / columns. But how would you do this if you have rather large table and it keeps changing.
The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature,
but we all know how the default settings of various Microsoft products are like).
• First select data part of the table you want to format.
• Go to Conditional formatting dialog (Menu > Format > Conditional
Formatting)
 • Change the “cell value is” to “formula is” (YES, you can base your formatting
outcome on formulas instead of cell values)
• Now, if you want to highlight alternative rows, the formula can go something
like this,
=MOD(ROW(),2)=0
which means, whenever row() of the current cell is even, to change the
coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
Also, if you want to highlight alternative columns instead of rows you
can use the column() formula.
What if you want to change background color of every 3rd row instead,
just use =MOD(ROW(),3)=0 instead. Just use your imagination.
• Set the format as you like, in my case I have used yellow color. When you are
done, the dialog should look something like this:

• Click OK.

• Congratulations, you have mastered a conditional formatting trick now

2. Creating a quick project plan / gantt chart using conditional formatting:

Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and
snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of
formulas you can do it no time.
• First create a table structure like shown above, with columns like Activity,
start and end day, day 1, 2,3, etc…
• Now, whenever a day falls between start and end day for a corresponding
activity, we need to highlight that row. For that we need to identify whether a
day falls between start and end. We can do that with the below formulas,
=IF(AND(F$8>=$D9, F$8<=$E9),"1","")
Which means, whenever, the day number represented on the top row is
between start and end we will in 1 in the corresponding cell.
 • Next, whenever the cell value is 1, we will just fill the cell with a favorite color
and change the font to same color, so that we dont see anything but a
highlighted cell, better still, whenever you change the start or end dates, the
color will change automatically. This will be done by conditional formatting
like below:

• Congratulations, you have mastered the art of creating excel gantt

charts now
3. Extreme In-cell Graphs:
Incell graphing is a nifty trick that basically uses REPT() function (used to repeat a string,
character given number of times) to generate bar-charts with in a cell. You can apply conditional
formatting on top of them to give the charts a good effect. Here is a sample:

The above is a table of visits to Pointy Haried Dilbert in the month of January 2008. As you
can see I have highlighted (by changing the font color to red and making it bold) for the cells that
have more than average number of visits in the month. I am not going to tell you how to do it, it
is your home work
4. Highlight mistakes / errors / omissions / repetitions using conditional
formatting:
Often we will do highly monotonous job like typing data in a sheet. Since the work is
monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided
by conditional formatting. I use this trick whenever I am typing something or pasting a formula
over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts,
could run in to thousands of rows across multiple states /regions etc.).
Lets see how you can highlight a cell when it has an error:
• First select the cells that you want to search for errors
• Next go to menu > format > conditional formatting and mention the formula
as: =iserror() (see below)

• In the same way you track repetitions, a simple countif() would do the
magic for you, or Omissions (again a countif())
• Thats it, you have learned how to save tons of time by letting excel do the job
for you. Sit back and sip that coffee before it gets cold.
5. Creating dash boards using excel conditional formatting:
As I said before you can use conditional formatting to create intuitive sales reports or analytics
outputs. Like the one shown here,

Here is how you can do it:

• Copy your data table to a new table.
• Empty the data part and replace it with formula that can go like this (I am
using the above table format to write these formulas, may change for your
data)
=ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever
the cell value is more than its predecessor in the data table we are
appending the symbol â–² (go to menu > insert > symbols and look for
the above one) etc.
• Next, conditionally change the color of cell to red / green / blue or pink (if you

want ) and you are done

• Show it to your boss, bask in the glory

I have created an excel sheet containing all these examples. Feel free to download the excel and
be a conditional formatting rock star.