How to Chart Daily Sales With Excel
By Jeff Lenning
Tracking and storing daily sales data in a table in Excel 2013 for Windows makes it easy to plot the data in a chart and apply filters as needed.
Tracking Daily Sales Data
Store the sales data in a table by selecting a cell within the data range and selecting the Insert > Table ribbon icon.
Confirm that Excel identified the correct data range in the resulting Create Table dialog box and that the data has a header row -- column labels such as "Date" and "Sales" -- and click OK. If Excel does not identify the correct data range, use your mouse to select the desired daily sales range. If your data range does not have column labels, clear the My Table Has Headers check box.
Confirm that Excel successfully converted the ordinary data range into a table by viewing the updated blue cell formatting and notice you now have a new Table Tools > Design ribbon tab. This ribbon tab provides many settings and options for the table. Clicking on a cell outside the table causes Excel to hide the Table Tools > Design ribbon tab. Selecting a cell within the table causes Excel to show the Table Tools > Design ribbon tab.
Creating the Chart
Select any cell within the table and select the desired chart type icon from the Insert ribbon tab. Typically, trends over time such as daily sales are effectively visualized with a line chart. To create a line chart based on the table data, select Insert > Line Chart and then the desired style.
Excel inserts the line chart into the worksheet and provides many options and settings for your new chart in the Chart Tools ribbon tabs; you can select the desired chart style, for example. Clicking out of the chart into a worksheet cell hides the Chart Tools ribbon tabs. Selecting the chart displays the Chart Tools ribbon tabs.
You can select the chart border and click and drag to move the chart within the worksheet or use the handles in the corners of the chart to resize it.
Move the chart out of the current worksheet to another worksheet or to its own sheet if you prefer. To do so, click the Chart Tools > Move Chart ribbon icon.
Select the desired location in the resulting Move Chart dialog and click OK.
Adding New Sales Data and Applying Filters
Add new sales data to the table and the chart by typing or pasting new data immediately under the table. Excel auto-expands the table and includes the new rows in the chart. If you skip a row when adding data, the table does not automatically expand. The new data needs to be entered or pasted immediately under the table without a blank row before it.
To select which data rows appear in the report, use the filter controls in the table's header row to select the desired values.
The chart automatically updates to display only the visible table rows based on the applied filters.
- Explore the Table Tools ribbon tab for additional table capabilities.
- To display sales goals on the chart as well as sales data, add a column to the table to store the target numbers and then create the chart.
- To display monthly data rather than daily data, update the table to store one row for each month instead of one row for each day.
- To display hourly sales data rather than daily sales data, update the table to store one row for each hour instead of one row for each day.
- To add a trendline to the chart, select the chart and then the Chart Tools > Design > Add Chart Element > Trendline ribbon icon.
Jeff Lenning CPA is the author of the Excel University series of books and related online training, and enjoys helping people use Excel more effectively. His articles have been featured in accounting industry publications and he is a Microsoft Certified Excel Expert and Microsoft Certified Trainer.