How to Create a Summary Chart in Excelby Darrin Koltow
Summary charts are graphical representations of summary data tables. These tables have at least one row that combines the numerical data of several previous rows. An example of a summary chart is a pie chart showing one company's total sales for the last quarter, created from a table of detailed sales data for that quarter. You can use the spreadsheet application Microsoft Excel to create a summary chart, once you summarize the chart's data table and prepare that table for Excel's chart wizard.
Open Microsoft Excel and create a new workbook by pressing "CNTL-N." Press "CNTL-S" to save the workbook, and name it "SummaryChartOfBookSalesByPublisher.xls" when prompted.
Create several rows of sample data representing the book sales of different publishers, across different types of books. Type or paste the following data into any worksheet in the workbook. Wherever you see a comma in this data, press the "Tab" key, which will place each item in its own cell.
Publisher,Genre,Distributor,Sales Dolphin Pub. pub,romance,Amazon,$456 Dolphin Pub. pub,romance,Dynamic Dist. dist,$65 Dolphin Pub. pub,how-to,Ma and Mo dist.,$87 Dolphin Pub. pub,how-to,Keemer and Son dist.,$654 Sanford Pub. pub,romance,Ma and Mo dist.,$123 Sanford Pub. pub,romance,Keemer and Son dist.,$789 Sanford Pub. pub,how-to,Ma and Mo dist.,$432 Sanford Pub. pub,how-to,Aunt May dist.,$767
Select the data table, then click the "Data" tab on Excel's main toolbar. Click the "Sort" icon to display the dialog box with sorting options.
Choose the following parameters for the "Sort" dialog box, to sort the publishing data by publisher names.
Select "Publisher" in the "Sort by" drop-down list. Select "Values" in the "Sort on" list. Select "A to Z" in the "Order" list.
Press "OK" to exit the dialog box and sort the data by publisher.
Press the "Data" tab on the Excel toolbar again, then click the "Subtotal" icon to display the dialog box for configuring summary totals for the publisher data. Choose the following options in the "Subtotal" dialog box:
Select "Publisher" for the drop-down list next to the text "At each change in:" Select "Sum" for the "Use function" drop-down list. Check the "Sales" checkbox for the "Add subtotal to" list.
Check the following checkboxes at the bottom of the "Subtotal" dialog box:
"Replace current subtotals" "Summary below data"
Press "OK" to create the summary rows for the publisher data. Notice that Excel has inserted boldface rows into the data, to add up the separate sales figures for each publisher.
Notice the small numbered buttons ("1," "2," "3") in the left pane of the main Excel window. Press the "2" button to hide the detail sales rows of the publisher data, and display only the summary sale data for the two publishers.
Select the publisher data, then press "Insert" on Excel's toolbar to display an array of icons for graphical objects that can be inserted in a worksheet.
Click the "Pie" icon in the "Charts" group of icons, then select the first 3D-chart icon under the "3D Pie" heading. Drag the resulting blank chart carefully away from the publisher data, then drag the new blue frame so it surrounds just the two sales data rows for "Dolphin" and "Sanford" publishing. Don't include the rows with the "Grand total" or the "Sales" column header.
Notice the slices of the finished pie chart, which now display the summary sales data for both publishers.
Items you will need
- photo_camera BananaStock/BananaStock/Getty Images