Home > Skills > Tables > Sample Data

Excel Sample Data Food Sales

This page has a Microsoft Excel sample dataset with sales orders for a fictional food sales company. You can download the sample data, to use for testing, Excel training and demos, and other learning activities.

Sample Data - Food Sales

This sample file has food sales data, from a fictional food company.

• There are 9 columns of data, including 1 column with a calculation.
• There are 244 rows of data in the food sales table.

Manual Entry Columns

Each row in the food sales data has the following 8 fields, for manual data entry:

• ID: Unique Order code for the order
• Date: Date the order was placed
• Region: geographic region where order will be shipped
• City: city where order will be shipped
• Category: product category - Bars, Cookies, Crackers or Snacks
• Product: product name
• Qty: number of units ordered (quantity)
• UnitPrice: product selling price per unit

The screen shot below shows the first 6 columns in the Food Sales dataset table.

Calculated Column

Each row in the food sales data also has one column with a formula calculation:

• TotalPrice: total price of order

The screen shot below shows the last 3 columns in the Food Sales dataset table. In cell I2, the following formula multiplies the number in the Qty column by the number in the UnitPrice column:

• =[@Qty]*[@UnitPrice]

Tip: I've added grey fill colour, and an orange heading cell, to column I, to show that it's different from the other columns, that are for manual entries.

Customize the Sample Data

You can use this food sales data as is, or make changes to the data, to customize it for your training sessions. For example:

• Change names for regions and cities, to localize the data for client training
• Add new food categories and products
• Revise the unit prices, to reflect local costs or currency
• Add new rows with more sales orders
• Delete existing rows of data, to create a smaller data set

See the video in the next section, for examples of using the Excel Find and Replace feature, to quickly modify existing data.

Video: Find and Replace with Wildcards

In this video, you'll see how to use the Excel Find and Replace commands, to clean up names in a contact list. For written steps, and the sample file, go to the Find and Replace page.

Video Timeline

• 00:00 List of Names to Clean Up
• 01:02 Make a Backup of Data
• 01:30 Remove Colons and Text
• 02:30 Remove Hyphens and Text
• 03:00 Remove OF and Text

Example: Food Sales Data Analysis

In addition to the food sales data file, there is a second sample file that you can download. The second file has the same food sales data, along with the following types of data analysis examples:

1. Summary formulas
2. Pivot tables
3. Pivot charts

1) Summary Formulas

In the data analysis sample file, there is a sheet named Totals, with summary formula examples.

• I entered the region names across the top, in cells C4 and D4.
• Then, in cells B5:B8, I entered the four category names.

To get the total quantities for each region, for each category, I entered this formula in cell C5:

• =SUMIFS(Sales_Data[Qty], Sales_Data[Category],$B5, Sales_Data[Region],C$4)

The formula calculates a total:

• from the food sales Qty column
• for rows where Category matches the value in cell B5,
• AND Region matches the value in cell C4

The formula from cell C5 was copied down to row 8, and across to column D.

Video: Sum Cells - Multiple Criteria

In Excel , you cn use the SUMIFS function to calculate a total for rows that meet two or more criteria. Watch this short video to see the steps, and there are written instructions on the Excel Sum Functions page.

2) Pivot Tables

In the data analysis sample file, there are two sheets where I've added pivot tables and pivot charts.

CatReg Sheet

On the sheet named CatReg, there's a pivot table with 3 fields:

• Category is in the Row area, down column A
• Region is in the Column area, across row 4
• TotalPrice is in the Values area, cells B5:D9

RegPct Sheet

On the sheet named RegPct, there's a small pivot table with 2 fields:

• Region is in the Row area, down column A
• Qty is in the Values area, cells B5:C7

Instead of showing the quantity numbers, I set that field to:

• Show Values As, % of Column Total

Tip: See more examples of using the Show Values Of setting options in a pivot table.

Video: Create a Pivot Table

Watch this video, to see the steps for creating a pivot table in Excel 2013 and later. There are written instructions. The video transcript is below the video, and for detailed steps, go to the Create a Pivot Table in Excel page.

3) Pivot Charts

In the data analysis sample file, there are two sheets where I've added pivot tables and pivot charts.

CatReg Sheet

On the CatReg sheet, there's a pivot chart based on the pivot table from that sheet.

I used the 2-D Stacked Bar chart type, and it has three fields:

• Category is on the vertical axis, at the left
• Region is in the Legend, showing East as blue and West as red
• TotalPrice is in the Values area, and the bar segments represent those values

Tip: There is another Pivot Chart example, and detailed steps, on the Pivot Chart Compare Years page

RegPct Sheet

On the RegPct sheet, there's a pivot chart based on the pivot table from that sheet.

I used the 2-D Pie chart type, and it has two fields:

• Region is the chart category, and forms the pie slices, showing East as blue and West as red
• Qty is in the Values area, and size of each slice represent those values

Tip: There are formatting tips for this type of chart on the Excel Pie Chart page.

Download Sample Data

Food Sales: Click here to get the food sales data file. The zipped Excel file is in xlsx format, and does not contain any macros.

Food Sales - Data Analysis: Click here to get the food sales data file, with pivot tables and pivot charts, analyzing the data. The zipped Excel file is in xlsx format, and does not contain any macros.

Related Links

Here are a few of my Contextures tutorials on working with data in Excel, organizing it in tables, and summarizing the data in pivot tables.

-- Excel Sample Data

-- Excel Tables

-- Pivot Tables

-- Data Entry Tips