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

Insert > Table
1

Store the sales data in a table by selecting a cell within the data range and selecting the Insert > Table ribbon icon.

Create Table Dialog
2

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.

Excel Table
3

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

Insert > Line Chart
1

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.

Daily Sales Chart
2

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 Chart
3

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.

Chart Location
4

Select the desired location in the resulting Move Chart dialog and click OK.

Adding New Sales Data and Applying Filters

Add New Sales Rows
1

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.

Filter to Show Desired Chart Rows
2

To select which data rows appear in the report, use the filter controls in the table's header row to select the desired values.

Updated Chart
3

The chart automatically updates to display only the visible table rows based on the applied filters.

Tips

  • check Explore the Table Tools ribbon tab for additional table capabilities.
  • check 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.
  • check 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.
  • check 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.
  • check To add a trendline to the chart, select the chart and then the Chart Tools > Design > Add Chart Element > Trendline ribbon icon.

About the Author

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.

Photo Credits

  • photo_camera Microsoft