Use Data Bars to Quickly Pinpoint Problems

This past week I have been working on this new analytical project and I tried some new stuff. Mostly it was new programming; I’m trying to get to the point where I can just whip up a program to do what I need, but I’m not there yet. It still takes me a couple of hours.

But I did try out some regular Excel features that I knew about but haven’t utilize yet. One was using the +/- in the pivot table to open up more details or close down the details. There are some issues using this feature, so I will have to think some more on how to incorporate it within some of the things I do (I have a lot of formulas referencing/finding/calculating things in the pivot table and if the detail is closed, then the formulas don’t work). Right now, that feature is not working the way I had hoped.

The other feature is the Data Bars that you find  under Conditional Formatting. You can also find it by highlighting the series of numbers and then clicking on the suggestion button that pops up, generally in the lower right. One of the suggestions will be Data Bars under the Formatting tab option. Either way, click on it and BOOM, you get the graph similar to what’s shown in the picture to the left. You can change the colors of the bars if you want.

This is an easy way to see at a glance which unit, item, category, or whatever is doing well or poorly relative to others. My posted image shows data bars on the variance between actuals and budget. If the actual expense is greater than the budget (negative variance), then the data bar for that item will be in red and will be to the left of the central line. Conversely, if the variance is positive, the bar will be in green and will be to the right of the central line. This graph helps you pinpoint problem areas without a lot of scrutinizing the numbers. You will still have to dig in to find out why but you will be able to prioritize where you want to drill down first. One little quirk: the data bars will be in the same column as your numbers and thus lay on top of them, so to see the numbers, you will have to adjust the column. The end result will be data bars sitting to the right or left of the numbers, depending on whether you have an unusually large negative or positive number.