VBA in Excel (2 Blogs) Become a Certified Professional

Data Visualization using MS Excel: All you need to know

Last updated on Jul 05,2019 249 Views

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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 blog is just for you. In this series of blogs, we will discuss how to do Data Visualization using MS Excel. This doesn’t just bring about a whole new means of visualizing data but also form a crucial part of any Excel Certification Program.

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

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.

Picture - Data Visualization using Excel - edureka

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.

Importing Data in Excel 1 - Data Visualization using Excel - edureka

  • 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:

Importing Data in Excel 2 - Data Visualization using Excel - edureka

  • 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 Home 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.

Importing Data in Excel 3 - Data Visualization using Excel - edureka

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

Importing Data in Excel 4 - Data Visualization using Excel - edureka

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.

Slicer 1 - Data Visualization using Excel - edureka

  • After clicking the Slicer, a dialog box called Insert Slicer will appear with all the attributes and a checkbox against them.

Slicer 2 - Data Visualization using Excel - edureka

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

Slicer 3 - Data Visualization using Excel - edureka

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

Slicer 4 - Data Visualization using Excel - edureka

  • To select multiple values, click on the icon on the top right (with three lines and checkbox).

Slicer 5 - Data Visualization using Excel - edureka

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.

Data Bars 1 - Data Visualization using Excel - edureka

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

Data Bars 2 - Data Visualization using Excel - edureka

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.

Color Scales 1 - Data Visualization using Excel - edureka

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

Color Scales 2 - Data Visualization using Excel - edureka

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.

Icon Sets 1 - Data Visualization using Excel - edureka

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

Icon Sets 2 - Data Visualization using Excel - edureka

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.

Top Bottom Rules 1 - Data Visualization using Excel - edureka

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

Top Bottom Rules 2 - Data Visualization using Excel - edureka

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

Top Bottom Rules 3 - Data Visualization using Excel - edureka

As illustrated in this blog, 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 blog, advanced data visualization techniques using MS Excel will be presented which will help in detailed data analysis.

Microsoft Excel is one of the simplest and powerful software applications available out there. The Advanced Excel Training Programme at Edureka helps you learn quantitative analysis, statistical analysis using the intuitive interface of MS Excel for data manipulation. The usage of MS Excel spans across different domains and professional requirements. There’s no better time than now to begin your journey!

Comments
0 Comments

Browse Categories

Subscribe to our Newsletter, and get personalized recommendations.