Chart Visualizations in Excel Power View

Power View is an Excel Visualization tool that allows you to build visually appealing graphs and charts, dashboards for management, and reports that can be issued daily, weekly, or monthly. When we think of Microsoft Excel, we think of various tools such as Formulae, which makes an analyst's job simpler, PivotTables, which allows the user to analyze data distributed across a vast number of columns and rows, graph library, which is as comprehensive as any other programming language and so on. 

This article will provide an overview of Chart visualizations.

Chart Visualization

There are several Chart choices in Power View. Power View's charts are interactive. Additionally, the Charts are interactive in a presentation context, allowing you to emphasize the analysis results dynamically. Charts can have numerous numeric variables and series. A chart's design choices include displaying and removing - labels, legends, and titles. 

The following chart visualizations are available in Power View:

• Scatter Chart
• Bubble Chart
• Pie Chart
• Line Chart
• Bar Chart
• Column Chart

Example

To create any chart follow the steps:

Step 1: First, enter data in the format shown below.

Step 2: Navigate to the Insert tab on the Excel ribbon and select the Power View option at the end of the list.

Step 3: When you select the Power View option, a power view report will be loaded and generated within the same worksheet. As illustrated in the picture below, you will have a power view report:

Step 4: Within Power View Fields, pick the Month, Quarter, and Sales Columns from the Range1 section. The ∑ sign is in the Quarter, Sales, and Percentage of Sales columns. It is present because those columns contain numeric data and may be summed.

Step 5: We will now include a graph. Navigate to the Design tab at the top of the ribbon to explore several design possibilities under the Power View Report sheet. Switch Visualization is one among them. This option allows you to include graphs in the Power View Report. Select the choice from the Other Chart.

Step 6: Select the Scatter option from the Other Chart dropdown menu.

Scatter Chart

Scatter charts are used to understand the correlation between two variables easily. 

A scatter chart always has two value axes: one set of numerical data running horizontally and another set of numerical data running vertically. 

Each dot in the scatter chart below represents a month, with the horizontal X axis representing the Quarter and the vertical Y axis representing Sales. Power view can distribute these data points evenly or unevenly across the horizontal axis.  It is determined by the data that the chart represents.

Advantages of the Scatter Chart

• Easy to understand.
• Simple.
• It does not get affected by external data.
• It is the initial step in understanding the relationship between two variables.

Limitations of the Scatter Chart

• Non-mathematical method.
• Can't be used for more than two variables.
• It does not give results in quantitative form.

Bubble Chart

Follow the same steps to get the bubble chart of months and quarters. But in the bubble chart, you need to add the third numeric column that defines the size.

A bubble chart, a scatterplot extension, is commonly used to demonstrate correlations between three or more numerical variables. A single data point is represented by a bubble chart. The value of each bubble is indicated by its 1) horizontal position on the x-axis, 2) vertical position on the y-axis, and 3) size. In animation, the color of the bubble or its movement can occasionally represent additional dimensions. You may modify the Scatter Chart view to the Bubble Chart presentation by adding a third numeric column that defines the size of the data points.

Advantages of Bubble Chart

• It can be used for the 3-Dimensional dataset.
• Different colors and sizes of bubbles attract the user.
• A bubble chart in Excel is better than a table format.

Limitations of Bubble Chart

• The overlapping of bubbles is the biggest problem.
• The size of the bubbles might confuse the user.
• It might be difficult for the user to understand as compared to other charts. 

Pie Chart

Follow the same steps to get the pie chart of months and quarters. In basic Excel the main role of pie chart is to build a part-whole relationship. In Power View, pie charts can be basic or complex. You may create a pie chart that drills down when you double-click a slice or one that displays sub-slices within the bigger color slices. A pie chart can be cross-filtered with another chart. Assume you choose a bar in a bar chart. The portion of the pie chart that corresponds to that bar is highlighted, while the remainder of the pie is greyed out. The Pie chart clearly shows that there are too many slices of months. Pie Charts operate best when the number of categories is 8 or less. By filtering the data, you may limit the number of categories.

Advantages of the Pie Chart

• Summarize large data in visual form.
• More Visually appealing than other types of graphs.
• Display relative proportions of multiple classes of data.

Limitations of the Pie Chart

• It becomes less effective if too many pieces of data are used. 
• It becomes complicated if more than one data set is compared, as this chart is used only for a single data set.
• This may lead to taking more time to analyze and assimilate the result.

Line Chart

Line charts are used to compare data points from many data series. Line charts align all category data along the horizontal (category) axis and all numerical value data along the vertical (value) axis. Months will be presented to the left or right, and the Line Chart will be displayed appropriately. Months are shown horizontally, while numerical value data is displayed vertically.

Advantages of a Line Chart

• They are easy to draw, read and compare.
• Can be used for a huge range of data.
• It can help us to show slight change also.

Limitations of a Line Chart

• It is not suitable for plotting fractions or decimal values.
• It can only be used for data that have values in numbers and total figures for example total salary of employees.
• It can only be used for inconsistent scales on the axis, which can result in accurate data.

Bar Chart

Go to the Design tab and, this time, pick Clustered Bar from the Bar Chart dropdown choice inside the Switch Visualization menu. This option allows you to see all of your sales and months in a single chart.

Bar charts are used to compare data points from many data series. In a Bar Chart, categories are grouped vertically and values are organized horizontally. Consider utilizing a bar chart in any of the following situations: You have one or more data series that you would want to plot,  You want to compare data from several categories, and The axis labels are somewhat lengthy. There are three types of bar charts in Power View: stacked, 100% stacked, and clustered. Months are shown vertically, whereas numerical data is displayed horizontally.

Advantages of Bar Chart

• Accessible to a wide audience.
• Quick and accurate estimating results.
• Summarize a huge data set in visual form.

Disadvantages of a Bar Graph 

• It does not indicate the critical activities of a project.
• Unnecessary additional information is required. 
• It can't be used as a control device, as it does not show progress in the report. 

Column Chart

A column chart may be used to depict data that is organized in columns or rows on a spreadsheet. Column charts are great for displaying data changes over time or presenting comparisons between things.

Column charts include horizontal axes for categories and vertical axes for values. There are three column chart subtypes available in Power View: stacked, 100% stacked, and clustered. 

The months are shown horizontally, while the numerical data is displayed vertically.

Power View's charts are interactive. When you click on a value in one of the charts in Power View, that value is highlighted, and all of the tables, matrices, and tiles in Power View are filtered to that value.

Advantages of Column Chart

• Helps when there are multiple values for each category.
• Easy to compare the values of each category.
• Useful for time series analysis.

Limitations of Column Chart

• It is difficult to understand the stack column chart as there is no common baseline for measurements.
• Key assumptions and patterns are not revealed.