Important: In Excel for Microsoft 365 and Excel 2021, Power View is removed on October 12, 2021. As an alternative, you can use the interactive visual experience provided by Power BI Desktop, which you can download for free. You can also easily Import Excel workbooks into Power BI Desktop.
In Power View in SharePoint 2013 and in Excel 2013, you can quickly create a variety of data visualizations, from tables and matrices to bar, column, and bubble charts, and sets of multiple charts. For every visualization you want to create, you start on a Power View sheet by creating a table, which you then easily convert to other visualizations, to find one best illustrates your data.
Excel offers a powerful yet easy way to create customized data visualizations that communicate insights from your data. Simple charts and graphs allow you to present complex data in a format that’s easier for audiences to digest.
With the visualization options in Excel, you can quickly turn stale datasets into eye-catching and informative graphics. A well-designed data visualization makes trends, relationships and anomalies in your data more understandable at a glance
In this post, I’ll provide a step-by-step guide to creating effective data visualizations in Excel.
6 Steps to Visualize Your Data in Excel
Follow these 6 tips to take your Excel data and turn it into compelling charts graphs and dashboards
Step 1: Organize Your Data
Ensure your data is properly organized in an Excel table with column headers and row labels. Clean your data by removing errors, duplicates and inconsistencies. Pivot, filter or aggregate your data as needed to summarize relevant information. Proper data prep makes visualizing easier.
Step 2: Select the Data to Visualize
Decide which data segments to highlight, then select the cell ranges accordingly. You may visualize a whole table or just specific rows and columns. Be selective to avoid overcrowded or confusing charts.
Step 3: Choose a Chart Type
Consider which chart or graph type is best to compare, correlate, rank or show trends and relationships in your data:
- Bar charts for comparing quantities
- Line graphs to visualize trends and changes over time
- Pie charts to show proportional breakdowns
- Scatter plots to reveal correlations
- Pivot charts to summarize and filter large datasets
Step 4: Insert the Chart
Go to the Insert tab and click the type of chart you want. Excel will create a default version from your selected data. The chart will be linked dynamically, so updating the data will refresh the visualization.
Step 5: Customize and Format the Chart
Enhance readability and aesthetics by formatting chart elements. Some options include:
- Add descriptive titles and axis labels
- Switch row/column orientation
- Change colors and fonts
- Resize, move or hide chart elements
- Add data labels and trend lines
- Set scalable axes
Step 6: Continually Edit and Update the Data
Don’t just create your chart and forget it. Actively curate your data visualizations by tweaking them and keeping data current. Refreshing datasets will make your Excel charts dynamic visual analytics tools.
Chart Types and When to Use Each
Here’s an overview of common Excel chart types and effective use cases for each:
Column Charts – Compare values across categories. Great for showing changes over time.
Line Charts – View trends and trajectory over time. Good for temporal data.
Pie Charts – Display proportional breakdowns or percentages of a whole.
Bar Charts – Also compare categorical data. Horizontal orientation works well.
Scatter Plots – Reveal correlations between two variable sets. Plot to find relationships.
Combo Charts – Combine two chart types for different data facets. Example: column and line chart.
Gauge Charts – Display progress toward goals, KPIs, metrics, etc.
Maps – Plot geographic data, positioning via longitude/latitude data.
Waterfall – Show cumulative builds and drops. Helpful for financial reports.
Funnel – View stages in a process. Useful for sales/marketing data.
Sparklines – Embed micro charts to show trends beside cells.
Design Best Practices for Visualizing Data in Excel
Keep these tips in mind when creating charts for better understanding and storytelling:
- Label charts clearly and include informative titles
- Choose colors purposefully and consistently
- Avoid clutter – only include essential chart elements
- Use appropriate axis scales and units
- Make key numbers and data points stand out through formatting
- Keep visualizations simple and focused on main takeaways
- Sort/filter data to show most relevant info upfront
- Leave outnice formatting touches like data table borders
Well-designed charts should make your data easy to interpret accurately. Prioritize simplicity, clarity and focus in your visualizations.
Common Excel Chart Applications
Excel charts and graphs have widespread business uses. Some examples include:
-
Sales reports – Bar or column charts to compare revenue results.
-
Financial analysis – Line charts to view profit trends over time.
-
Marketing campaign analysis – Gauge charts to show campaign KPI progress.
-
Product quality analysis – Control charts to assess deviations.
-
Scientific data analysis – Scatter plots and trend lines to find correlations.
-
Economic analysis – Maps to plot socioeconomic datasets geographically.
-
Supply chain monitoring – Gantt charts and sparklines to track production.
The possibilities are virtually endless for bringing your Excel data to life visually!
Visualizing information makes data more accessible, easy to absorb and impactful. With Excel’s extensive formatting options and chart types, you can create meaningful views of your data for clearer insights.
Examples of visualizations available in Power View
- Slicer filtering the report to breads
- Tile flow navigation for tiles, currently on croissant
- Card in a tile container, filtered to the current tile (croissant)
- Line chart in tile container showing quantities consumed and server, filtered to croissants January to December
- Multiples, filtered to breads and sorted in descending order by quantity served
- Column chart filtered to breads, showing quantities served and consumed
Power View offers a number of chart options: pie, column, bar, line, scatter, and bubble. Charts can have multiple numeric fields and multiple series. You have several design options in a chart—showing and hiding labels, legends, and titles.
Charts are interactive: As you click values in one chart, you:
- Highlight that value in that chart.
- Filter to that value in all the tables, matrices, and tiles in the report.
- Highlight that value in all the other charts in the report.
Charts are interactive in a presentation setting, too—for example, in reading and full-screen modes in Power View in SharePoint or in a Power View sheet in an Excel workbook saved to Excel Services or viewed in Microsoft 365.
Pie charts are simple or sophisticated in Power View. You can make a pie chart that drills down when you double-click a slice, or a pie chart that shows sub-slices within the larger color slices. You can cross-filter a pie chart with another chart. Say you click a bar in a bar chart. The part of the pie chart that applies to that bar is highlighted, and the rest of the pie is grayed. Read about pie charts in Power View.
Scatter and bubble charts
Scatter and bubble charts are a great way to display a lot of related data in one chart. In scatter charts, the x-axis displays one numeric field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart.
In a bubble chart, a third numeric field controls the size of the data points. You can add a “play” axis to a scatter or bubble chart, too, to view data as it changes over time.
More about Bubble and scatter charts in Power View.
15 Creating Stock Chart – Data Visualization in Excel Tutorial
How do I create effective data visualizations in Excel?
Here are some tips to help you create effective data visualizations in Excel: Your data visualization can help organize and visualize even the most complex data sets, but ensure you’re using accurate information for the best results. Confirm that you’ve correctly inputted the data into Excel from your original source.
How do I use a Visio data visualization template?
In the Visio desktop app, select a Data Visualizer template and then select the Excel data template link to open Excel. Then select Create to open the Visio Data Visualizer template. Use the Excel data template to create tables for your processes and sub-processes.
How do I create a data visualizer diagram?
To create a Data Visualizer diagram, you can work simultaneously in Excel and Visio. You might find it easier to work in two windows on your Windows device or in a multi-monitor environment. Visually comparing your work between Excel and Visio helps confirm you are doing what you intend to do.
