Data Visualization & Conditional Formatting in Excel

Upasana Priyadarshiny
Edureka
Published in
7 min readJun 19, 2019

In today’s world, data is the new currency. Data is everywhere, and hence visualizing data is the first step in making sense out of data. If you ever wanted to do quick data visualization without the need of having a separate (license-based) tool, then this article is just for you. In this series of articles, we will discuss how to do Data Visualization using MS Excel.

In this article, you will get an insight into the ease of using MS Excel for data visualization. We shall discuss the following topics:

  • Why Data Visualization for MS Excel?
  • How to import data in excel?
  • Visual Filtering using Slicer
  • Bring life to your data using Conditional Formatting

Now the first question which comes to mind is,

Why Data Visualization using MS Excel?

There are many reasons why using Excel is an ideal choice for Data Visualization including but not limited to the following;

  • No need to pay separate license cost for other tools (as it is safe to assume that most of us have MS Excel installed on their computers)
  • Easy to use since there is no need to learn a separate tool
  • Easy to share the visualization (for example dashboard) with others as the receivers also don’t need a separate visualization tool.

Initial Data Visualization using Excel: Importing data in Excel

Many of the data files are directly in the .xlsx format so we can directly work with them. However, for other formats like CSV, etc Excel provides an easy to import them.

  • Open a new instance of MS Excel and on the top ribbon, click on the icon of Data. After clicking on it, you will get the following screen.
  • Excel provides an easy way to import the data from various formats like CSV, XML, JSON etc.
  • Click on the relevant data source. Choose the correct import options (for ex the delimiter in a CSV file), and the data will get imported into excel.For example: after importing a CSV file into excel, the data looks like this:
  • The next important step after loading the data is to convert it into a table.
  • To convert the data into a table, go to the icon and then click on the Format as Table option. Select the desired format and then select the entire data as the range. The data looks like this after converting into table format.
  • After converting to the table format, the data looks much more readable. Moreover, Excel recognizes it as Table which means that a lot of functions can be applied directly on the data like a filter, sort etc.

Initial Data Visualization using Excel: Visual filters using Slicer

One of the common operations which need to be performed on data is to apply data filters. Although filters can be applied by clicking on each attribute name, there is a better and more visual way of doing it i.e. the Slicer.

  • Click on the Insert icon and then click on the Slicer icon.
  • After clicking the Slicer, a dialog box called Insert Slicer will appear with all the attributes and a checkbox against them.
  • Click on the desired attribute name and press Ok. Please note that multiple attributes can also be selected. In this example, select Location and then press Ok.
  • After pressing Ok, a new dialog box with the name as Location gets opened. All the various Locations which are present in the data set can now be seen in the dialog box. Just click on any of the values to filter the data by that value.
  • To select multiple values, click on the icon on the top right (with three lines and checkbox).

Initial Data Visualization using Excel: Bringing life to your data using Conditional formatting

Conditional formatting helps in visually exploring and analyzing data, detecting issues, and identifying trends and patterns. Using Conditional formatting, it is very easy to highlight interesting cells or ranges of cells and visualize data by using data bars, color scales, and icon sets.

1. Data Bars

A data bar helps to analyze the value of a cell in comparison to other cells. The length of the data bar is directly proportional to the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower values, especially with large volumes of data.

  • Select all the values for any column whose values you can explore using the data bar. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Data Bars option and select the desired color.
  • After selecting the desired color, the data in Total Revenue column will look like the below snapshot. Please observe that now just by eyeballing the data, it is very easy to make out the patterns in Total Revenue attribute.

2. Color Scales

Color scales are visual guides that helps in understanding data distribution and variation. A three-color scale helps in comparing a range of cells using a gradation of three colors. The shade of the color represents the higher, middle, or lower values. Similarly, in two-color scale, the shade of the color represents higher or lower values.

  • Select all the values for any column whose values you can explore using color scales. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Color Scales option and select the desired color style.
  • After selecting the desired color style, the data in Temperature column like the below snapshot. Please observe that Excel displays the cooler temperatures in green, the hotter temperatures in red and the middle-temperature ranges in yellow.

3. Icon Sets

You can use Icon sets to classify data into various categories separated by a threshold value. Each icon represents a range of values.

  • Select all the values for any column whose values you can explore as icon sets. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Icon Sets option and select the desired style.
  • After selecting the desired icon style of your choice, the data in Pamphlets columns will look like the snapshot below. Please observe that the star is filled as per the number of pamphlets distributed.

4. Top/Bottom Rules

You basically use Top/Bottom rules to quickly find and highlight top values, bottom values, average values etc.

  • Select all the values for any column wherein the Top or bottom values have to be highlighted. After selecting the values, click on Conditional Formatting Icon under the Home Menu. Once the drop-down appears, click on Top/Bottom Rules option and then multiple options can be seen.
  • After selecting the Top 10% option, select the desired color style. The data in Total Sales column will look like the below snapshot. In the illustration below, the top 10% values (2 values out of 22 values) are highlighted.
  • We can apply multiple rules in the same attribute. For example, the below illustration shows the top 10% values in red and bottom 10% values in yellow.

As illustrated in this article, MS Excel is a powerful tool which provides many useful data visualization options like Slicer, Data Bars, Color Scales, Icon sets, Top/Bottom Rules etc. Using these options, one can quickly analyze data patterns and visually explore the data.

Hence, next time you are given some data, try using these options to make more sense out of your data. In the next article, advanced data visualization techniques using MS Excel will be presented which will help in detailed data analysis.

If you wish to check out more articles on the market’s most trending technologies like Artificial Intelligence, DevOps, Ethical Hacking, then you can refer to Edureka’s official site.

Do look out for other articles in this series which will explain the various other aspects of Excel.

1. Advanced Data Visualization Using Excel

Originally published at https://www.edureka.co on June 19, 2019.

--

--