Insights

Better Visualize Your Data Analytics With Microsoft Excel

 

Better Visualize Your Data Analytics With Microsoft Excel

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

Excel

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. 
 Excel

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


Excel

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.

Excel 

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

Excel
 
  

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.

Excel

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.

Author(s)

Richard McClure
Richard McClure
Manager
View Profile