Prepare your data before you build a pivot table
Want to master pivot tables, Power Query and dashboards in weeks? The Advanced Excel course at Cokonet Academy teaches them on real business files.
Quick answer: Pivot tables need a tidy table: one header row, one record per row, no blank rows or columns, no merged cells and consistent data types in each column. Convert the range to an Excel Table with Ctrl + T so new rows are picked up automatically.
Typical source data is a sales register with columns for Date, Invoice No, Customer, Region, Product, Quantity, Amount and GST Rate. Dates must be real dates, not text, or grouping by month will fail. Numbers stored as text are the most common reason a pivot shows Count instead of Sum.
Spend five minutes on a quick check before every pivot: sort each column to spot blanks and odd values, use Find to catch stray spaces, and confirm the totals of the raw data with a simple SUM. That habit saves you from presenting a wrong summary to your manager. If the same file arrives every month, clean it once in Power Query and refresh, rather than fixing it by hand each time.
Pivot tables in Excel step by step
Quick answer: Insert the pivot, place fields in the four areas (Filters, Columns, Rows, Values), set the summary type and format the numbers. The whole build takes about a minute once your data is clean.
- Select any cell in the table.
- Go to Insert > PivotTable. Excel selects the table range.
- Choose New Worksheet and click OK.
- In the PivotTable Fields pane, drag Region to Rows.
- Drag Amount to Values. Excel shows Sum of Amount.
- Drag Product to Columns to see a region by product grid.
- Drag Customer to Filters to view one customer at a time.
- Right-click a value, choose Number Format and apply Indian comma style.
Newer Excel versions also offer Recommended PivotTables, a quick way to see suggested layouts.
Summarise values: sum, count, average and percentage
Quick answer: Right-click any value and use Summarize Values By for Sum, Count, Average, Max or Min, or Show Values As for % of Grand Total, % of Column, Running Total and Difference From.
To compare regions, use % of Grand Total. To track monthly growth, put months in Rows and use Difference From the previous month. To count invoices instead of adding amounts, drag Invoice No to Values and choose Count. These options answer most management questions without a single formula.
Group dates and numbers in a pivot table
Quick answer: Put a date field in Rows, right-click a date and choose Group, then select Months, Quarters and Years. For numbers such as invoice values, group into ranges like 0 to 10,000, 10,000 to 50,000 and so on.
Indian businesses often need financial-year views from April to March. One simple way is to add a helper column in the source with a formula that returns the financial year, then use it in Rows. Grouping fails if any cell in the date column is text or blank, so check the source first.
Calculated fields, slicers and PivotCharts
Quick answer: Calculated fields add formulas inside the pivot, slicers add clickable filters, and PivotCharts turn the summary into a chart that updates with it. Together they make a simple dashboard.
- Calculated field: PivotTable Analyze > Fields, Items and Sets > Calculated Field. Example: GST Amount = Amount * GST Rate.
- Slicer: PivotTable Analyze > Insert Slicer, choose Region or Month. Connect one slicer to several pivots through Report Connections.
- Timeline: a slicer for dates that filters by month, quarter or year.
- PivotChart: PivotTable Analyze > PivotChart. Choose a clustered column or line chart.
For bigger datasets or several related tables, Power Pivot and the Data Model let you build relationships, the same idea used in Power BI. You can see where that leads on our Data Analytics course page.
Pivot table examples for jobs in Kerala
Quick answer: Interviewers ask for region-wise sales, top customers, vendor ageing, GST rate-wise sales and monthly expense summaries. Practise these five and you will handle most Excel tests.
| Task | Rows | Columns | Values |
|---|
| Region-wise sales | Region | Month | Sum of Amount |
| Top 10 customers | Customer (Top 10 filter) | None | Sum of Amount |
| Vendor ageing | Vendor | Age bucket | Sum of Outstanding |
| GST rate-wise sales | GST Rate | Month | Taxable value and GST |
| Expense summary | Expense Head | Quarter | Sum of Amount |
Our guide to the Excel test for job interviews in Kerala lists the other skills employers check. Browse every programme we run on the all courses page.
Common pivot table problems and fixes
Quick answer: If new data is missing, refresh (Alt + F5) or use a Table as the source. If values show Count, fix numbers stored as text. If grouping fails, clean blank or text dates.
- Blank row labels: fill empty cells in the source.
- Duplicate items: trim spaces with TRIM or Power Query.
- Wrong totals: check for subtotal rows copied into the source data.
- Layout keeps changing: untick Autofit column widths in PivotTable Options.
Want hands-on practice with a trainer checking your files? Book a free consultation or call +91 8075 400 500.
Why Cokonet Academy for Excel and analytics training
Cokonet Academy is an NSDC partner training institute with campuses in Trivandrum and Kochi. Find us on Google: Cokonet Academy, Trivandrum and Cokonet Academy, Kochi.
- NSDC partner training institute with campuses in Trivandrum and Kochi, plus a Bengaluru centre and online-live batches
- 11,200+ successful placements and 50+ corporate hiring partners
- Expert trainers in SAP (FICO, MM, ABAP), AWS, Python with AI, Data Analytics and Power BI
- Placement support until you are hired, with mock interviews and resume reviews
Advanced Excel at Cokonet is taught on real business files: sales registers, ledgers and HR sheets, so PivotTables, Power Query and dashboards are practised on the kind of data your first employer will hand you.
Course details
- Course: Advanced Excel, Cokonet Academy
- Duration: 6 to 8 weeks, weekday and weekend live batches
- Mode: classroom in Trivandrum and Kochi, or online-live; English with Malayalam support
- Covers: XLOOKUP and dynamic arrays, PivotTables and Power Pivot, Power Query, interactive dashboards, forecasting and VBA macros
- Next step: the natural first step into the Data Analytics course (SQL, Power BI, Python)
- Placement: support until you are hired
Modules and batch dates: Advanced Excel course in Kerala.
Visit our training centres
Book a free consultation and sit in on a live session before you decide. We are open Monday to Saturday, 9:30am to 10:00pm.
Enrol now
Build pivot tables, Power Query models and dashboards on real business data. See the course and next batch, visit Cokonet Academy, or call and WhatsApp +91 8075 400 500.
