Articles & E-Books


Better Visualize Your Data Analytics With Microsoft Excel

Jul 20, 2018

Microsoft Excel has always been a great tool for performing data analysis. Improvements over the years have made it even easier to add visual graphics to your data to enhance its readability and the quality of its presentation. Below are some of those use cases and how to apply them to your data set.

The Icon Data Set


The table above has been set up to show current-year sales and prior-year sales to clients. To create your icon data set, you must first create a formula in Column E.  The formula I am using is: =IF(ABS([@2018]-[@2017])<1000,1,IF([@2018]>[@2017],2,0))
This formula will show any difference of under $1,000 in yellow, with green for trending up and red for trending down. 

Once you create your formula, highlight Column E, go to Conditional Formating, and click on Icon Sets. The image on the above shows which properties to set to determine which color of arrows to use.
Format Your Numbers Quickly


Many times when I get into an Excel file, I see tables like the image above. Giving it a better visual for the user requires only a few quick steps to apply some simple formatting. To start, highlight the first two numbers in Row 2 and click on the “$” on the Home tab. Then, highlight the remaining numbers and click on the “,” sign. After that, highlight all the numbers and reduce the decimal place to “0.”

You can then highlight all the data and apply the table format that will give it the coloring you want and the sorting/filter buttons in the column headers, as shown in the second image below.


Using Data Scales and Color Scales to Quickly Visualize Your Data
Similar to the icon sets, you can easily apply some conditional formating and use color scales to visually improve graphics for your data. In Column E, I set up the formula to see by 1,000s how sales grew or declined. I then used the Color Scales feature in Conditional Formatting to show the data, which you can see in the image below


With data scales, you can highlight whole columns of data and then click on Conditional Formatting and select Data Bars. This gives you the ability to see the increase/decrease on a graphic within the cell, as shown in the image below.


These tools allow anyone to apply some simple graphics that let them see their data from a new perspective instead of simply looking at the numbers.


Richard L. McClure
Senior Manager
View Profile