Excel Data Visualization: Mastering 7 Spiffy Charts and Graphs

Do people groan whenever your spreadsheets are opened to share wonderful data insights? You're not the only one to blame.

Perhaps you have BBS- Bland and Boring Syndrome. Serving the same old pie slices to your guests is no longer acceptable. People prefer that. Make it more interesting by serving doughnuts or displaying a waterfall. These 7 charts and graphs can spiff up your boring spreadsheet presentation making your meeting remarkable.

1. You can see your data at a glance thanks to Sparklines

Emily, an Excel Assignment Help specialist, says, “Data visualization in cells gives you an immediate visual context of your spreadsheet data. Excel 2013 introduces the Sparkline feature, which displays win/loss charts, columns of data, or line graphs from rows and columns of cells. Easy as pie!”

• You can play with the formatting options in the Ribbon to create the look you desire by selecting the Sparkline chart cell, clicking the Design item, and selecting the desired formatting.
• The cells will display data in in-cell charts. Click OK.
• If you select the text in that box, you can use your mouse to click a row or column of cells to show or hide the Sparkline graphs.
• Choose the data rows or columns you want to visualize and click in the Data Range text box.
• Click Insert in the UI ribbon, select Line in the Sparklines group (you can also choose Column or Win/Loss), after which you should select a range of cells next to the data you want to chart.

2. Be smart and put your knowledge to work Table

Thanks to Excel's tables, with the robust Filter feature, even the largest data sets can be sorted and filtered with a single mouse click. It also enables great charting capabilities. The linked chart will automatically expand to include the new data series when a new column is added to the Table. Neat.

• In the Ribbon menu, select Insert, then Table when you want to table-ize a certain range of data. To enable headers on the Table, check the box under My Table Has Headers. Click OK. Ta-da! The data is formatted nicely, and the header row has drop-down controls for filtering.
• Put a new header row item in the cells below your data, along with some underlying data, with your embedded graph displayed next to it.
• A new item will be added to the chart axis once you hit return in the header row cell. Charts update as you enter new data in each cell.

Tables also make Charts even more useful by incorporating Filters. The Filter drop-down in the State column can be used to display only Ohio offices, for instance. Consider a table containing 100 field offices' monthly budget figures. Click the drop-down, uncheck Select All in the next step, and then scroll to Ohio in the list and select it. The chart updates as soon as you click OK.

3. Clear and concise waterfall charts

Chanel, who provides Data Flow Diagrams Assignment Help to stat students, exclaims, "The waterfall chart is a new type of chart added to Excel 2016 in Office 2016. Visualizing income and expenses or tracking portfolio value over time can be done to show how positive and negative values contribute to a total. Waterfall charts emphasize individual gains and losses, in contrast to line charts, which show trends."

• A waterfall chart can be made using a two-column array with positive and negative dollar amounts in the left column.
• Click on Insert, then Recommended Charts once you've selected the array.
• In the Insert Charts window, click the All Charts tab.
• On the right, click Waterfall. You'll see a chart with rather poor formatting.
• Next, let's trim the Start aEmilt so that other data is not overwhelmed by it.
• Choose Axis Format from the menu when the Y-axis labels are selected.
• To calculate the axis values, enter 15400 in the Axis Options pane and click OK.
• Select the Number item, and then click Category. Select Currency, then type zero to the decimal place and hit Enter.
• Let us not forget the first data point should be Total.

A total can be set by selecting the Series Options icon in the Format Data Point window. Make sure that only the first column is highlighted (the others should look faded) by clicking twice on the first column in the chart. Click the grayed-out column to set the total.

4. Yum... donuts

There is nothing worse than pie charts. Stackable column charts are visually more refined but crowded, reducing smaller data points to wedges that are hard to understand. More importantly, 3D pies and mathematical slices can cause viewers to feel ill at ease because of their extravagant appearance.

If you find yourself pulling out an Excel pie chart the next time, serve doughnuts. Donut charts depict contributions to a total similar to pie charts. The corporate charting space is a refreshing change of pace due to the additional white space and the less common use.

• Creating a doughnut chart is as simple as selecting your data, clicking Insert, choosing Pie or Doughnut, and then selecting Doughnut Chart.
• Click Format Data Series under the right-click menu when you click the chart body. Changing the Doughnut Hole Size to about 60% can be done in the first pane that appears.

5. A thermometer chart shows your progress toward your goals

Are you looking for a visual representation of your achievement? Let's say you want to raise $30,000. An excellent tool, in this case, is a thermometer chart. A simple layout will consist of a cell on the left for Goals, a cell for Donations, and a cell on the right for $16,425 and $30,000. Then click the Column Chart icon in the Ribbon and select Clustered Column Chart in the list.

• To display a “1” in the X-axis label, click the Switch Row/Column icon in the Ribbon.
• Click the Format Data Series button in the smaller Donations bar.
• Click the Series Option window to open the secondary axes. A secondary Y-axis label appears behind the smaller Goal bar.
• When you right-click on the left axis, choose Format Axis and enter 30,000 in the Maximum box, the left axis will be formatted. You can repeat the process for the right (secondary) axis.
• Fill the Goal bar with no fill and set the Border to a black line by clicking the Fill & Line icon, right-clicking the box, and selecting No fill from the menu.
• The donations bar should be formatted with your choice of fill color and no border.
• Eliminate the labels from the X-axis and the right axis of the chart.
• In the Format Axis pane, you should also tighten up the left axis. Select the Number Decimal places check box in Axis Options so that it is set to 0. Select the checkboxes you wish to use.

6. You can create your own Gantt chart for tracking your projects

Louise, a data structure assignment help expert, exclaims, “Gannt charts are made from stacked bar charts. They are the best chart feature of excel!”

• Across each of the top four columns, write “Start,” “Stage,” “Date,” and “On Task.”
• Plan, Build, Approve, etc., are stages of a project entered in Cells B2 through B7.
• Then, in Cells D2, D3, D4, and D7 (under On Task), enter the number of days needed for each stage to be completed. Then, format Cell C2 as General with "=A2".
• Excel's time/date code, 43205, helps with chart layout.
• Copy and paste "=C2+D2" into cells C4 through C7, then enter this formula in C3.
• Columns B through D of the Table should be selected and inserted.
• To view the stacked bar chart, click the column chart icon. The chart will now be brought into focus. The X-axis labels should now be formatted by clicking the Format Axis button.
• Click on the Number item in the Axis Options pane, and then select Date from Category.
• Enter the first item in your list in the Minimum text box, located under Bounds, at the top of the pane. Then choose the short format from Type. A value of 43205 would be appropriate.
• It's in reverse order, as you will see. Select Axis Format from the right-click menu.
• On the Axis Options screen, select the Reverse Order checkbox under the Categories heading.

7. There's nothing better than sunburst charts!

With the addition of new sunburst chart types in Excel 2016, data relationships can be visualized, nested hierarchies of data can be taken into account, and granular elements can be used to populate larger data groups. This can be compared to a multilevel pie chart.

Excel sunburst charts are built upon reading your data set structure and interpreting it. A fictional company, for instance, has three markets (industrial, specialized, and consumer) that track the sales of its three product lines (widgets, controllers, connectors). These categories are then broken down by industry (automotive, medical, etc.). Click All Charts, and then click Sunburst on the Recommended Charts tab once the data is properly laid out.

The Conclusion

Microsoft Excel has more DataViz capabilities than you may realize. Here are seven charts that can help others understand what you're talking about by presenting facts in creative ways. Show rather than tell! They don't make people squint or overthink things; instead, these graphs do to your data what a photograph does to everything else: they make it look beautiful.