Excel Tips and Tricks

How to: Data Bars in Excel

I’m going to teach you some easy excel tricks to make your budget look amazing while adding sweet functionality to make budgeting fun and easy.

If you’ve seen my family budget, you might have noticed the awesome bars on my numbers.  These are called data bars and they can be a great tool for assessing your budget and spending habits at a glance!

Why use data bars?

Data bars look really appealing and help you visualize your spending without actually having to add in any graphs or other graphics.  They sit right there with the numbers, giving you immediate feedback the moment you type in your numbers for the month.

In the snip below, you can see quickly that my two highest spending categories are “Groceries and home” and “Other” at a glance.

You can see the numbers too, but on a zoomed out excel sheet, the data bars really help the high-spending categories pop!

If you don’t have a budget set up in excel yet, start here.  You can set it up in about ten minutes, then pop back here to learn how to make the sweet data bars.  And don’t worry, it’s super easy!If you’re really in a hurry, you can download the basic budget from my google share here, however, I strongly recommend reading through the original post so you understand how the formulas work.  This budget is designed as an example and should be personalized for your different expenses and income.

Adding data bars to your budget

Data bars are calculated in relevance to a grouping.  This means that, in the above group, each data bar represents the percentage of the cell it is in relative to the other cells in the grouping.

That sounds a little complicated, but basically what you need to know is data bars have to be set up in groups.  

So lets pull up our handy-dandy basic excel budget.  It should look somewhat like this, but hopefully with your own different expense and income categories plugged in!

Remember that we need to set these up in groups, so start by highlighting all of the expense cells.  Under the “Home” tab, look for the “Conditional Formatting” menu.

Click on “Conditional Formatting” and select “Data Bars.”  If you want to keep it simple, you can pick one of the existing options.  I picked a “Gradient Fill” in red.

How easy was that?

Custom data bars

If you want to customize the bars like I did in my personal budget, click “Conditional Formatting” then “Data Bars” then “More Rules…” at the bottom of the menu.  This should pull up this menu:

You can actually do lots of cool customization with this menu, but I mostly prefer to make cosmetic customization.  I only change the Bar Appearance settings.  Here is what I use – 

And this is the final result!

Colorizing categories

Then you can select the top row and change the color, to make it all look a little more cohesive.

Adding some color to the other category rows makes everything look all color-coordinated and fun!

How easy was that?  You can add all this customization in under three minutes but it makes your budget nicer to look at and funner to use!  

You can use different excel tricks, like icons or color gradients, to help show you spending trends as well.  Would you be interested in learning to do that?  Are there any other excel tips you want to learn?  Let me know in the comments!

Tagged

Leave a Reply

Your email address will not be published. Required fields are marked *