Conditional Formatting for Dashboards

This week I wanted to take a closer look at conditional formatting and point out some simple ways to represent data.

In the below worksheet I have values between 1 and 100 in column A.

I have used different versions of conditional formatting to represent the data in a variety of ways.

To start I pass my data into columns C, E and I using a simple formula =A2. I then copy that formula down to the remaining 19 rows. To create each column I do the following.

RANK

I create my conditional formatting by using a Format all cells based on their values rule. I select the Icon Sets as the format style. For the Icon style I select 3 Traffic Lights (Unrimmed). Selecting the drop down arrow for Icon Style will give you many different graphic representations that you can select from.

I tweak the values and Type and when done I select the OK button and then apply the conditional formatting by clicking on the apply button.

3 Color Scale Data Bars and 2 Color Scale

All three of these are Format Style variations.

I create my conditional formatting by using a Format all cells based on their values rule. I select one of the format styles and tweak the colors as needed.

Using these simple conditional formatting options can give your reporting and dashboards an added punch of style.

How have you used conditional formatting in your reporting?

Password

Excel allows you to determine who can open a workbook and  who can modify one. These options are available to you when you save your file.

From the office button located in the top left corner of Excel File menu, select Save As...

The Save As dialog box appears. In the lower left hand corner of this dialog box, left mouse click on the Tools button, then select General Options...

To prevent unauthorized people from opening the file, type a password in the Password to open box.  

To prevent unauthorized people from modifying the file, type a password in the Password to modify box.

You can use different passwords for each of these options. 

This would be useful when you want only a select group of people to be able to access the file but only a subset of these people will have the ability to edit the file. 

An example of this type of dual password setup would be when you want the people on your team to be able to open the document but only want the managers to be able to edit the document.

At third option is a Read-only recommendation. When selected, Excel will prompt the user that the file should be opened as read only.

 The file will still be available for editing but would need to be saved with a different name.

When done, select the OK button. If you enterer in a password in either or both of the password boxes, you will be prompted with a confirm password dialog box. If you entered passwords into both boxes, you will receive two confirm password prompts. The first will be for password to open, the second for Password to modify.

Remove extra blank spaces from text

You can use the =TRIM function to remove blank spaces before and after text. This handy function will clean your data and allow you to use the clean data in other functions such as =VLOOKUP. In the below example the original cell contents have blank spaces to the left. Using the trim function =TRIM(A2) in cell C2, I can remove them. 

Alignment Tab

If you are new to Excel, you will use the Alignment tab quite often. Found on the Home Tab of the Ribbon (Excel 2007 version), the Alignment tab allows you to position (justify) text in a cell.











From Top left to right, Horizontal alignment of text (top of cell, centered, and lower. The bottom left to right left justify, center and right justify.

Other features of the alignment tab include indenting text, wrapping text in a cell and merging multiple cells (basically creating one large cell). You can even change the text direction from the Alignment tab.

Clicking on the lower right hand corner of the tab will bring up the Format Cells dialog window to even give you more formatting options.

Best Dashboard

In a previous post I demonstrated a simple but effective dashboard but did not go into how to create it. 

Sales Dashboard

I will do so in the following post.

As with any dashboard, it all starts with the data you have and what you are trying to convey. A good dashboard will be easy to understand, (clear and concise). It will convey the data in as few steps as needed without losing key points. It will also not obfuscate key points by surrounding them with superfluous information.

For my demonstration, I will create almost the entire dashboard from one key data series (Sales per Month).

Month	Sales
Jan	$  20,947
Feb	$  45,494
Mar	$  35,173
Apr	$  20,249
May	$  47,792
Jun	$  42,511
Jul	$  28,343
Aug	$  28,343
Sep	$  28,000
Oct	$  27,161
Nov	$  17,864
Dec	$  29,987

Now to start my dashboard I first need to set a color theme. I personally don’t like dashboards that use dozens of colors. In my opinion they tend to end up looking like something from a circus. I prefer a sleek look and try to minimize my color pallet. For this example I will use variations on shades of blue.

So now that I have my data, know what my key points are, and color tablet; I can now begin.

I am going to stop for a moment and vent a bit… Quite often managers don’t know how long it actually takes to create a useful dashboard. A good dashboard is so simple to understand that the development side is often overlooked. A general rule of thumb is that the design and planning of the dashboard will take about 70 percent of the time it takes to create it. The other 30 percent is the actual creation. A good dashboard is almost always used over and over so it needs to be easily updated (so be sure to plan ahead).

So we plan what we are going to design and then design what we plan.

Now having said that, don’t allow yourself to be pigeonholed by your initial plan. Some times as you actually create the dashboard, inspiration strikes.  But I digress….

To start I first highlight the entire worksheet and change the background to white. This removes the gridlines. I then enter in my data In cells C3..D15 and then format the data to be more visual appealing. I then tweak the data by merging cells B2, C2, D2 and E2. I enter the Sales in this merged area. I also add the Quarter for each Month in column B. In my dashboard I am using the Calendar quarters so Jan Feb and March are Q1, Apr May and June are Q2, etc…

I use the Boarders tool to outline my cells and fill ever other cell so they are shaded light blue. I bold and shade the Header cells grey and am pleased with the following result. If your data has cents, you may want to remove them to help streamline the look.

.

So far the data looks nice but it does not easily tell a story. I want to quickly know how the data is trending. I don’t want to have to visually compare each row so I want to add trending arrow indicators in column E.

What’s a trend indicator? Well it’s just like it sounds. Some text or graphic to indicated a direction of increase or decrease for a particular metric.

To add these trend indicators, I use a technique I explained in a prior post. Click on the link for instructions on how to accomplish this.

Trend Indicators

Don’t want to click on the link??? The formula you need in cell E5 is:

=D3 & " " & IF(D2<D3,"▲", IF(D2=D3,"*","▼"))

Copy this formula down through E15. Use conditional formatting to change the color. What’s that, you don’t know how to do that? Click on the above link (go on, it won’t hurt)…

Next I want to work the second portion of my dashboard, Sales by Quarter. If you have followed my instructions for the above, then this is just a repeat only with a consolidated result.

Q1 Q2 Q3 and Q4 in cells B 18 – B 21. In D18 I sum the first three months of my above data (cells D4, D5 & D6). For cell D19 (Q2) I sum (D7, D8 and D9), etc…

I then merge Cells B17 and C17 adding the title “Quarter”.  I also merge cells D17 and D18 and add the title “Sales”. I format the same as the above (blue background fill, outlining the cells and bolding / shading my headers grey).

I again use my trend line technique to compare the Quarter results.

Now I have displayed sales by Month but also summarized by Quarter including trend indicators for quick reference on how sales are trending by month and quarter.

Next up is the Summary section of the Dashboard. This is where I place my key points. I group them in one area for easy data access. I also center these key points toward the top. Now perhaps I could apply some heat map logic used for websites to optimize this focal point but I feel they best are represented in this dash board where I placed them(above and to the left of the graphics)

To create the summary I just repeat the formatting of the first two parts of the dashboard and use simple formulas to create the lookups. You will also notice that to create the Summary, I have a row for Sale Target for Year. This is hard coded but could be a lookup to a budget worksheet.

Again all of the formulas use basic Excel functions….

=AVERAGE(Sheet1!D4:D15)

=MAX(Sheet1!D4:D15)

=MIN(Sheet1!D4:D15)

=MAX(Sheet1!D18:D21)

=MIN(Sheet1!D18:D21)

=SUM(D4:D15)

For the lookups I could have used a combination of Indirect and Match as described in the following post…

Vlookup To The Left

However I opted to use helper cells. In the below image I have changed the font color from White to dark red for the helper cells to help you better understand the formulas. Now all of the helper cells are done by a function. For example cell F4 has the formula =C4. This way if I sort my data, the helper cells also sort.

Next on the dashboard, I create the Sales By Quarter bar chart and size it to fit.

Same with the Sales by Month bar chart (with trend line). No great mystery on creating a bar chart. To add the trend line (Excel 2007) from the ribbon select the Layout tab and under Analysis, select Trendline.

I format the Trendline as Polynomial with Order 4 to give it a smooth look.

Now all that’s left to create is the sweet little Sales by Month gauge / summary board in the upper right corner.

WARNING HERE COMES THE CONTROVERSY!!!!

People either love or hate the “Fuel Chart” or “Speedometer Chart”. I will concede that it can be misused and is usually not my first choice in graphs. However, used correctly it can be a powerful tool. 

Besides a billion automobiles can’t be all wrong, if they were, we would all have bar charts on our car displays….

Now I won’t go into detail on how to get the Current, Trend, Goal info. That’s all covered above and done with simple lookups. But the speedometer graph is done by overlaying a donut chart with a pie chart.

To create this graphic, see my next post on creating a speedometer graph.

The other day I was asked if I could break out numbers from a cell that contained both text and numbers. My manager wanted the numbers in one column and the text to the right in the adjacent column.

I thought sure, easy to do with a combination of =Left =Mid and = Right functions. However, when I looked at the data I noticed that the numbers did not start in the same position in the text. I also noticed that some cells had 6 numbers while others had 4. To make things even more difficult, some cells started with numbers first.

Well I thought about the problem and decided to solve it by converting the formula into an array.

What’s an array formula?

An array formula is a formula that works with array data values rather than a single data value. 

There are two different types of array formulas

1.     Formulas that work with an array or series of data and aggregate the result. Sum, Average and Count are good examples of this type as they all return a single value to a single cell.

2.     Formulas that returns an array of numbers as its result. These array formulas are entered into multiple cells that are then treated as a group. For example, =ROW(A1:E20).

In the above example I have my original data in column A. I first use the formula in column B to extract the numbers in the string. After entering the formula, I hold down the control key, shift key and enter key all at the same time to convert the formula into an array.

Once I had that, it was a simple matter of getting the length of how many numbers were in the original data and the starting position of the numbers in the string (Columns C and D).

Based on that, I could then parse out the text to the right of the number (Column E).

Now to accomplish all this I needed to use 7 functions….

=IF

=MID

=MATCH

=ROW

=LEN

=COUNT

=ISNUMBER

All of these functions are simple to use but the trick was combining them to get my solution.

Indeed knowing what functions are available in Excel will allow you to join them to solve almost any problem you have to solve. So it’s a good idea to learn them or at least know they exist so that you can reference them.

{=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1)))}

{=MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0)}

{=IF(MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0) = 1,MID(A2,(E2+D2),90),MID(A2,(E2+D2+1),90))}

How would you have solved the above problem?