The Status Bar

Status bars, as I've plainly decided on calling them, are a great way to express many forms of data, from reaching goals to comparing deadlines. You can use them on Gantt charts, work back schedules, or really any type of report.

While you may not have an immediate practical use for including a status bar on your excel documents, the visualization may quantify your goals in a better manner than dry numbers, and impress more visually-oriented clients, co-workers or managers.

Before you start, there are a few things you'll need to ask yourself:

• What variables (time, percentages, budgets, etc.) are you generating a status bar for?
• How many status bars are required for each item on your document?
• To which degree of accuracy does your status bar need to be?

---

Step #1. Determine the location of your status bar.

It's important to determine where you'll place your status bar early on, because the starting and ending points of each bar are dependent on the column number in which they're in. In this case, my status bar is going to begin in column E. 

Step #2. Determine the degree of accuracy for your status bar.

The next step is to create the space in which your status bar will exist. This is why you need to know what each cell is going to represent. Typically, using one hundred 1% cells is unnecessarily accurate, and four 25% cells may misrepresent data. Most of the time, I will make fifty 2% cells or twenty 5% cells. In my example, I'll create twenty 5% cells. Highlight Columns E through X (watching the tooltip indicating the number of columns selected), and navigate to the Home tab, select Format in the Cells pane, and choose Column Width. A width of 1 unit is generally a good place to start - you may change this later.

Step #3. Determine the first part of your conditional formatting formula.

Click Conditional Formatting in the Styles pane and choose New Rule. We'll select "Use a formula to determine which cells to format", and input the following formula:

=(COLUMN(E3)-COLUMN($E$1))*5+5

What we're doing is turning each column into a number on a scale. By relatively subtracting E1 from $E$1, we should see an ascending number list if we were to paste this formula across a row of cells. The first 5, which is multiplying the difference between the columns, determines increments your status bar will move by, and the second 5, which is added, determines the starting place of your status bar. If you were creating 25 4% cells, you would change your formula to:

=(COLUMN(E1)-COLUMN($E$1))*4+4

 This will be the first half of your conditional formatting. The rest will come shortly. In the mean time, pick a colour, and select where your formula applies. For now, we'll keep it simple and write $E$3:$X$5. You should end up with the following screen:

Step #4. Input your variables and calculate your status.

The final step is to actually write something into the "Hours Complete" and "Total Hours" cells. In my example, I've written "4" in C3 and "20" in D3, which of course would represent 20%. We now change our conditional formatting formula to reflect this by colouring in any cells where this percentage is greater than or equal to each column's value:

=(COLUMN(E3)-COLUMN($E$1))*5+5<=$C3/$D3*100

Your screen should now look the same as the image below. By manipulating the numbers in your columns, you can experiment with different combinations of numbers, and begin to look creatively at how you may use status bars in the future. As you'll notice, typing any combination of numbers between the range of C3:D5 will now generate a status bar.

---

Below are a few creative ways I have found to implement status bars into my reports:

Changing the bar's colour based on percent complete.

Using formula driven cells to display the percent of hours complete at the end of each status bar.

Displaying two bars per item. The grey bar represents the expected status based on the dates provided measured against the current date. The coloured bars represent the item's actual status based on the hours provided. Green bars appear only when meeting or passing the current expectation, and red bars appear when behind the expected schedule.