So, you need some eye-popping visuals to show off your top sales numbers for that meeting in 40 minutes but data, not design, is your forte. No problem. With Excel 2013—even if you’ve never used it—you can pump out a sophisticated, professional chart as fast as you can type. Or, if you can copy and paste the data from another source, you can produce a chart or graph in about 10 minutes (or less). Here’s how you do it.
To get started, open Excel and select the Blank worksheet.
Open Excel and select a blank workbook.
Create and format data
For this example, we’ll create a simple worksheet that displays the total July sales of each sales agent on the marketing team, then let Excel create several charts to compare the totals. All you have to do now is enter the data.
1. In cell A1 type: July Sales Totals by Sales Agent
2. In cells A3 thru F3, type: Mike H, Cesar C, Mark H, Kelly A, Kenny S, Jeff F (or whatever six names you prefer).
3. Highlight cells A3 through F3, then select Format > Column Width, type 12, and click OK.
4. With the same cells still highlighted, click the ‘center’ icon in the Alignment group on the Ribbon bar (to center the names inside each cell).
Format the chart data.
Now that you know the matrix range is A through F, you can center the title with a merge cells, center command. Highlight A1 through F1, click the Merge Cells icon in the Alignment group on the Ribbon bar, and choose Merge & Center. Or, you can skip all these formatting steps because the chart doesn’t use the spreadsheet formats. It only uses the column labels (names) and the data (numbers).
Center the titles.
Enter the numbers
The fastest and easiest way to enter data into Excel is top to bottom. because when you press the Enter key, the cursor automatically goes down to the next cell (and also because we generally read numbers in columns rather than rows). Now you can add some numbers to your range or use these:
In cell A4 thru A9, type: 6100, 8734, 6295, 6788, 7175, 7980
In cell B4 thru B9, type: 6245, 6162, 5942, 5895, 6968, 7112
In cell C4 thru C9, type: 7851, 7613, 6522, 5451, 6676, 8617
In cell D4 thru D9, type: 5658, 5574, 6579, 6275, 7278, 8418
In cell E4 thru E9, type: 7515, 7218, 7675, 6968, 7724, 7989
In cell F4 thru F9, type: 4998, 5856, 5987, 6816, 7210, 7614
Numbers automatically justify to the right, but if you prefer to center the numbers under the names, highlight the range from A4 through F9, and click the Center icon in the Alignment group on the Ribbon bar.
Create the chart/graph
Once the data is entered, Excel does all the work—all you have to do is choose the chart you like the best. If you want the sales agents’ names included in the chart, highlight the range including the row with the names—in this case, A3 through F9.
Select the Insert tab, then choose the Recommended Charts icon in the Charts group on the Ribbon bar.
Create the chart/graph.
Chart/graph options
The Insert Chart window appears, showing a dozen or so options for various charts. Scroll down through the window, click a chart format, then click OK.
Excel’s recommended chart and graph types.
The chart/graph appears on top of your spreadsheet. Notice the title just says “Chart Title.” Place your cursor over these words, click once, and then type a new title. Once finished, just click outside the chart somewhere to apply it. Click the chart again, and several Options buttons appear to the left of the chart. Click the Chart Elements button (it looks like a ‘+’ sign) to modify any of the chart elements. Each element on the list has another submenu for changes.
Enter the chart name and modify its elements.
Click the Chart Styles button (the icon is a paintbrush) to change the style or color of your chart. Styles include hollow columns, different backgrounds, reverse video, gradients, and other options. The colors can be various or monochromatic.
Modify the chart style and color.
Click the Chart Filters button (the icon looks like a funnel) to alter one of the sales agents’ names or any of the values (numbers) in the chart. For example, you can correct a typo, change Kenny to Kenneth, or re-enter an inaccurate number.
Change the chart/graph data.
But all of this is just cosmetics. The easiest method is to just add the data and choose a recommended chart, then print it.
Move, size, and print
To move the chart or graph away from the data, click anywhere in the white periphery of the chart (not in the chart data area), hold down the left mouse button, and drag the chart. To size it, click anywhere in the chart’s periphery, position the cursor on one of the corner handles that appears, and drag to the size you need. To print it, click the chart so the handles are visible, then click File > Print > Print.
Print the chart/graph.
Additional chart/graph types
If you can’t find a chart you like in the Recommended Charts window, highlight the range again, then choose the All Charts tab from the Insert Chart window. Excel provides 10 different chart types, such as bar, pie, column, area, etc.
Select the Insert tab, then choose the Recommended Charts icon in the Charts group on the Ribbon bar. On the Insert Chart screen, select the All Charts tab, and the All Charts menu screen appears with dozens of different types and styles. Notice (at the top of each chart type screen) that each type provides several chart styles. Follow the steps above under the Chart Options section to customize your chart and print it.
Lots of chart & graph types and styles to choose.
Total sales only
If you want to chart the total sales only, this one is even easier. Move your cursor to cell A10 and enter this formula into that cell: =sum(A4:A9) and press Enter. With your cursor still in cell A10, select Copy, move the cursor to B10 and highlight through F10, then choose Paste.
Now mover your cursor to A4 and highlight the range through F9. Select Format > Hide & Unhide > Hide Rows and everything disappears but the names and the totals of each column. To get a chart with just the totals and the sales agents’ names, you have to exclude the other data from the chart range (or hide it).
Highlight the remaining two rows—the names and the totals (A3 and A10 through F3 and F10). Select the Insert tab, then choose the Recommended Charts icon in the Charts group on the Ribbon bar. For a really impressive 3D chart, choose the All Charts tab and select one of the Types and Styles from this menu. Click the design you like, then click OK.
Choose a 3D chart form the All Charts menu.
Now you have a chart that maps just the individual agents’ total sales for the month of July. From this chart, you can more accurately see who had the highest and lowest sales for the month. Looks like Kenny wins by a hair. Don’t forget to change the chart title as explained above. Then move, enlarge, and print.
:3D chart of agents’ sales totals (only) for July.
Easy 5-minute shortcut
If you need only the total sales, just type the agents’ names in one row (A3 through F3) with the sales totals under their names (A4 through F4), then follow the instructions for creating the chart. For example:
Highlight the two rows containing the names and the totals (A3 through F4). Select the Insert tab, then choose the Recommended Charts icon in the Charts group on the Ribbon bar. Choose the All Charts tab and select one of the Types and Styles from this menu. Click the design you like, then click OK, and print. Including the data entry and the printing, five minutes tops!
Import data
If you already have the data for your chart or graph as a text file (or in another Excel spreadsheet), you can highlight the data in that original location, then copy and paste it into a new Excel spreadsheet. Highlight the range and follow the instructions above to create your chart or graph.
Once you create your first chart, you’ll never need instructions again—it’s that easy.