I’ve always believed that Excel is one of the most powerful software tools out there. It isn’t just the fact that it’s spreadsheet software, which is by its nature powerful anyway. No, Microsoft Excel 2013 itself has such an awesome collection of built-in tools and features that it has beyond any doubt, become one of the most useful tools for so many things. In this article, I plan to show you how powerful formulas and conditional formatting can be, with three pretty useful examples.

We’ve covered a number of different ways to make better use of Excel, such as using it to create your own calendar template, using it as a goals management tool, and other unique ways that you can use it to manage your life. Just read those articles and you’ll see just how powerful Excel can be.

Much of the power really lies behind the formulas and rules that you can write to manipulate data and information automatically, regardless of what data you insert into the spreadsheet.

Digging Into Excel

With the right information, you can create a system that automatically calculates and recalculates results and reports from that raw data. Today, I’d like to dig a little further under the surface and show you how you can use some of the underlying formulas and other tools to make better use of Excel.

Cool Conditional Formatting With Formulas

One of the tools that I think people just don’t use often enough is Conditional Formatting. With the use of formulas, rules, or just a few really simple settings, you can transform a spreadsheet into an automated dashboard that shows you a lot about the information in the spreadsheet at just a glance.

To get to Conditional Formatting, you just click on the Home tab, and click on the “Conditional Formatting” toolbar icon.

Under conditional formatting, there are a bunch of options. Most of these are beyond the scope of this particular article, but the majority of them are about highlighting, coloring or shading cells based on the data within that cell. This is probably the most common use of conditional formatting – doing things like turning a cell red using logical less-than or greater-than formulas.

One of the lesser used – or known about – conditional formatting tools is the icon sets option, which provides you with a great set of icons you can use to turn an Excel data cell into a dashboard display icon.

I discovered this once I upgraded to Office 2013 and used conditional formatting in Excel 2013. I checked out the icon sets and saw these cool LED indicator lights that I had only really seen before in some of the factory automation displays I’ve programmed. When you click on “Manage rules”, it’ll take you to the Conditional Formatting Rules Manager. Depending on the data you selected before choosing the icon set, you’ll see the cell indicated in the Manager window, with the icon set you just chose.

When you click on “Edit Rule…”, you see the dialog where the magic happens. This is where you can create the logical formula and equations that will display the dashboard icon you want. In my example, I monitor time spent on different tasks versus my budgeted time. If I’ve gone over half my budget, I want a yellow light to display, and if I’m over budget, I want it to go red.