At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
This is the final result we will create, a concise report that displays total sales per region in each row and per product in each column, complete with filters to select specific sales and a neat Indonesian rupiah display. Everything is created from a regular transaction table. Let's start. Hello, welcome to the Excel for beginners tutorial, pivot table, sales report in a flash. Imagine we have dozens of transaction rows, then our boss asks, what is the total sales per region, and which product is the most popular. With a pivot table, the answer appears in seconds, without any formulas at all.
Step 1: Sales transaction data
This is the sales transaction data for the first quarter. There are 40 rows, with columns for date, salesperson name, region, product, quantity, and total sales. The data is still messy because it is recorded in the order of events. The date column lacks full date formatting, indicating the column is too narrow. Double-click the column boundary line A to adjust the width appropriately. The main requirement for a pivot table is three. The first row contains column headers, there are no empty columns, and there are no empty rows in the data.
Step 2: Insert PivotTable
Click one cell within the data, anywhere. Open the Insert tab, then click the pivot table button in the top-left corner. A dialog box appears. Notice, Excel already recognizes our entire table, from A1 to F41. The location option is New Worksheet, meaning the report will be created in a new worksheet, so the original data remains untouched. Click OK. Now a blank sheet appears along with the pivot table fields panel on the right side. This panel is where we will arrange our report.
Step 3: Total sales per region
First, we want to know the total sales per region. In the right panel, check the region. The region names appear directly in a row on the left, this is the rows area. Then check total. Excel sums up the total sales for each region, this is the values area. With just two clicks, we already have a report. The western region turns out to be the largest, and below there is a grand total, which is the overall total.
Step 4: Products as columns
Now we want to know the sales of each product in each region. Right-click on the product name, then select add to column labels. Products, that is sugar, coffee, milk, and tea, now line up as columns. Each cell in the middle shows the sales of one product in one region. This type of report is called a crosstab, and it is very helpful for comparison. We can directly see which product performs best in which region. What if the boss only wants to see the sales of one salesperson?
Step 5: Filter by salesperson
Right-click on the sales fill, then select add to report filter. A filter labeled sales appears at the top of the report. By selecting a name from this filter, the entire report recalculates specifically for that salesperson. One report can be used to view anyone, without creating a new table.
Step 6: Format in Indonesian Rupiah
The sales numbers are still hard to read because there is no thousand separator. Let's format them as Indonesian rupiah. Select all the number cells in the report via the name box, then press Ctrl + 1. Select the custom category, and type the rupiah format, which is quotation mark, rupiah, space, quotation mark, hash, period, two hash marks, and zero. Click OK. Now all numbers display as rupiah with a thousand separator, much easier to read. One important thing that beginners often forget, pivot tables do not automatically update when the original data changes.
Step 7: Refresh PivotTable to update
Let's verify that. Switch to the transactions sheet with Ctrl + Page Down, then change the total sales in the second row to Rp999.000. Return to the report sheet. The numbers have not changed. Click on any cell within the report, then click the refresh button in the PivotTable Analyze tab. The numbers are now updated. Get into the habit of pressing refresh every time the source data changes.
Step 8: Save the report
The report is now complete. Press Ctrl + S to save. Remember the workflow. Prepare a neat table, insert a PivotTable, check the required fill options, set its position as rows, columns, or filters, then tidy up the formatting.
Done! Sales report from one table
Done. We have created a sales report using a PivotTable, summarizing by region and product, filtering sales, applying the Indonesian Rupiah format, and updating the data. Thank you for watching. See you in other tutorial series. Want to become proficient in Excel for work and business? Cerdas Inovasi Digital offers Excel and Office courses, template creation services, reports, and dashboards, as well as selling the practice files for this tutorial. Contact us via WhatsApp, or visit cerdasinovasidigital.com Thank you for watching. See you in other tutorial series. Thank you for watching.
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,279 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
