Skip to content
New Milap Nagar, Ambala City

Home Courses

Practical course guide · Ambala, Haryana

Advanced Excel Course in Ambala

Learn to organise, analyse and present data confidently with practical Microsoft Excel exercises.

What you build in Advanced Excel

Build a monthly sales report from a small transaction register that contains the kinds of problems found in ordinary office files. You will repair dates and text, calculate net sales, match a product catalogue, summarise the numbers, and compare the report total with the source. The same habits apply to stock, attendance, fees or service records. Use the worked example to practise before working with real records. The shop, amounts and product IDs in this guide are sample data.

This course suits students, office staff and job seekers in Ambala who can already enter data and write a simple formula. A useful starting exercise is to calculate line amounts for three products, fill the formula down and check the total by hand. If the result changes when you insert a row, or you lose track of which cells a formula refers to, spend time on Excel Tables and references first. Progress through the course by producing a workbook that another person can refresh and explain.

Set up a workbook you can audit

Define the unit represented by one row

Use one row per product line on a sales invoice. A customer who buys two product types should have two rows with the same invoice number and different line IDs. A repeated invoice number is therefore expected. A repeated invoice number and line ID should be reviewed against the original receipt. Write this rule on a worksheet named ReadMe. It helps the next person distinguish duplicate imports from legitimate extra lines.

Use columns for Invoice, Line, SaleDate, SKU, Qty, UnitPrice and Discount. Enter sale dates as actual Excel dates, quantities and prices as numbers, and discounts as decimal percentages such as 10%. An ID like 0007 should be kept as text when the leading zeros identify a product. If you import it as the number 7, formatting it with leading zeros changes how it looks in Excel, while the underlying value will still compare differently from text 0007 in some operations.

Keep the source and working copy easy to compare

Place the original values on a sheet named Raw and keep a dated copy of the original file. Create a Sales sheet with the working copy, then format its range as an Excel Table named Sales. Create a Products table on a Catalogue sheet with SKU, Product and Category. A Report sheet holds the summaries and a Checks sheet holds the key control numbers. Freeze headers on the working sheet and use meaningful column names so a reviewer can find the original field for any total.

Add five sample sales. On 2 August, invoice A101 line 1 contains four units of N01 at ₹50 with no discount, and its line 2 contains two units of C02 at ₹250 with a 10% discount. On 3 August, A102 contains three units of N01 at ₹50 without a discount. On 6 August, A103 contains one H03 at ₹400 without a discount. On 8 August, A104 contains one C02 at ₹250 without a discount. Catalogue categories are Stationery for N01, Accessories for C02 and Accessories for H03. You will use these five rows to check the work before adding more.

Clean the data and confirm what changed

Read dates, prices and identifiers correctly

Use a check column with =ISNUMBER([@SaleDate]) to flag imported dates that stayed as text. Individual cells that display 08/06 can be ambiguous when the source uses a different day and month order. Verify the provider's format with two dates that include a day above 12. When importing text, select the correct locale or date interpretation before converting a whole column. Open a known transaction and compare the displayed date with the original receipt after conversion.

Check prices with ISNUMBER and inspect any values that fail. A cell containing a currency symbol copied from an email can look like a price without behaving as a number. In a separate column, remove known stray characters and convert values using the decimal and grouping separators expected in that file. Try your approach on 250, 1,250 and 1,250.50 when those formats appear in the source. Save rejected cells for reference instead of silently deleting their rows.

To clean product IDs, compare the length of the source and cleaned values. =UPPER(TRIM(SUBSTITUTE([@SKU],CHAR(160)," "))) can remove common outer spaces, turn a nonbreaking space into a normal space and standardise case. Apply the same rule to the catalogue. If your supplier uses meaningful spaces or case-sensitive codes, agree on the normalisation rule first. Keep a record of IDs that still fail to match so the owner can correct the catalogue.

Handle duplicates and invalid entries deliberately

Add a check that counts the combination of Invoice and Line using COUNTIFS. A count above one raises a review flag. Filter the flagged records and compare their product, quantity and price with the source. If two attached exports overlap, removing an identical repeat may be appropriate. If the same key refers to different transactions, ask which source is authoritative. After the decision, record the affected ID and reason on the Checks sheet.

Separate blank fields from legitimate zeros. An empty discount on an imported row might mean the source omitted it. A recorded 0% means no discount applied. Decide with the data owner how blanks should be handled. Use Data Validation to constrain quantities and discounts entered into the shared copy, then keep a separate check for blanks or out-of-range values because copied data can bypass input restrictions. Try a pasted value of -5% and an empty cell before sharing the workbook.

Write formulas that work when the table grows

Use calculated columns and explicit assumptions

Add NetSales and use =ROUND([@Qty]*[@UnitPrice]*(1-[@Discount]),2). The first product line should return ₹200. The second returns ₹450 because the two cables cost ₹500 in total before a ₹50 discount. With all five sample lines, expected net sales are ₹1,450. Rounding at the line level should match the rule used by the sample source. Ask how the source handles rounding before reconciling real transactions.

When comparing sales against a fixed target, place the target in a labelled input cell. Refer to it as $B$2 so the reference stays fixed when the formula is copied. A relative reference like B2 changes as you copy it. Mixed references such as $B2 and B$2 keep one part fixed, which can help when a report grid uses row labels for categories and column labels for months. Test the copied formula in one corner of the grid before filling the whole area.

Try adding a row immediately below the table and change a price on one of the older rows. The calculated column should cover the new row and the total should reflect both changes. If one formula cell no longer follows the pattern, examine it for a hard-coded value or a manually altered cell reference. Protect the formulas only after the workbook works correctly and colleagues know which fields they are expected to edit.

Match products with exact lookups

Where available, use =XLOOKUP([@SKU],Products[SKU],Products[Category],"Check SKU",0) to return a category. The zero selects an exact match and the supplied message makes a missing key visible. Practise the same task using INDEX and MATCH with an exact-match argument of zero for installations without XLOOKUP. VLOOKUP with FALSE can also work when the lookup column is at the left of the selected range.

An exact match assumes a usable catalogue key. Test what happens when N01 appears twice in the Products table with two categories. You may still get one result, giving the report a misleading appearance of certainty. Add a catalogue check that counts each SKU and require a count of one for every active product. Try an unknown SKU and confirm that it appears in an exceptions list. Avoid making every lookup error display as an empty cell. Archive unresolved items where they can be investigated.

Summarise the month and prove that it balances

Create a report with conditional aggregation

Put the first day of the month in B2 and a category such as Stationery in A6. Use =SUMIFS(Sales[NetSales],Sales[Category],A6,Sales[SaleDate],">="&$B$2,Sales[SaleDate],"<"&EDATE($B$2,1)). The starting date is inclusive and the next month's first day is excluded. This works even when imported date cells include a time of day. Try a receipt exactly on the first day of the next month and check that it stays outside the current total.

With the five sample rows, Stationery should total ₹350 and Accessories ₹1,100. Together they give ₹1,450. Accessories contributes approximately 75.9% of net sales in this sample. Label the figure as share of net sales and divide by the whole report total. Put a note beside the measure indicating that it represents only the selected month and data loaded into this workbook. If the amount is zero, decide how an undefined share should display.

Use COUNTIFS when you need the number of matching lines. The sample contains five lines and four distinct invoices. If a colleague asks for average sales per invoice, divide by four for ₹362.50. Dividing by five gives ₹290 per line, which answers a different question. To practise distinct counting, create a small separate summary with one row per invoice and use it as a second check on the calculation.

Read a PivotTable and its filter state

Create a PivotTable from Sales and place Category in Rows and NetSales in Values. Make the calculation Sum, then apply the selected month to SaleDate. Excel may default to Count if a numeric field contains text. Compare the grand total and both category totals to the formula report. If the numbers disagree, show the PivotTable filters and compare which dates were included.

Change one price or add a test transaction and refresh the PivotTable. A regular PivotTable stores a snapshot of its source, so editing the underlying table alone does not necessarily change the PivotTable display immediately. Verify that the pivot references the table and that the test row falls within the filter. If totals look unexpectedly high, inspect the detail rows to identify the source lines. Remove all test rows and refresh again before delivering the file.

Build a report people can read quickly

Connect charts and measures to questions

Use a horizontal bar chart to compare Stationery with Accessories. Label the chart “Net sales by category” and show currency clearly. Use an axis starting at zero for this comparison because bar length conveys amount. A line chart can answer a different question once you have enough history: how monthly totals change over time. Use complete periods and label any partial month visibly when including it.

Show three figures near the top of the Report sheet: net sales, invoice count and average sale per invoice. Include the period and refresh date above them. A manager looking at the sample should be able to identify ₹1,450, four invoices and ₹362.50 without opening any formula. Ask a partner to read the report aloud. If they report five invoices, check what the label says and whether they have confused lines with invoices.

Apply conditional formatting to useful exceptions

Highlight unknown products or invalid discounts using a short warning label as well as colour. A simple icon or text makes the message understandable when printed in grayscale or for readers who cannot distinguish red and green easily. Check the “applies to” range after adding rows and test a copy-and-paste operation. Use a consistent date format and figures with an appropriate number of decimal places, and check that the column width allows the full date to show.

When exporting a report for a meeting, set the print area to the summary, review the page breaks and print preview, and confirm the period. Preserve enough text size for the person reading a printed copy. Keep the monthly export separate from the editable workbook. If a colleague needs to see the calculations, provide the workbook through the agreed workflow with the relevant source or a sample version as appropriate.

Explain a change from one month to the next

Create a comparison sheet after the second month is ready. Place the old and new periods in the column headers and calculate the amount change and percentage change for each category. If Accessories moves from ₹1,100 to ₹1,320, the increase is ₹220, or 20% of the earlier month's sales. If a new category starts at zero, label its percentage change as unavailable and show the actual amount. Keep the difference between two values visible alongside the percentage to help readers judge the size of the change.

To find a possible reason for the difference, break down changes by product and check units, prices and discounts. Imagine that a product's monthly sales grow from ₹500 to ₹660. If the old month had five units at ₹100 and the new one had six at ₹110 with no discount, the ₹160 change includes ₹100 from the extra unit at the old price and ₹60 from applying the new price to six units. Show the arithmetic on the comparison sheet and state the order you used. When a range of products changes, report the contribution from the largest verified changes first.

Review warnings before writing a conclusion. If the data file arrived on the tenth of a month, compare a matched period or wait for the full month before interpreting a slowdown. A single day with a large school order can influence the monthly result. Check that the corresponding sale exists and include it in the explanation when relevant. Describe what the workbook measures and which questions would need more information.

Leave instructions for a colleague who inherits the report

Put the steps for opening, refreshing and checking the workbook on its ReadMe tab. Include the name of the required source file, the expected headers, how to tell which month is visible, and whom to ask about an unknown product. Provide the expected number of rows and ending sales total for the sample run. Use consistent worksheet names and place frequently updated settings next to the report labels.

Have a colleague perform the handover from a fresh copy while you observe. They should be able to choose the right input, refresh it, spot a deliberately added exception and produce the requested chart. Note anything they have to guess. Revise the instructions and repeat the check with a new source file. For a workbook used by several people, agree who owns monthly updates and changes to the catalogue.

Practise a repeatable import and refresh process

Load a file with Power Query where available

Use a spare copy of the report when trying a new import. Import one CSV into Power Query, confirm the delimiter and headers, and set column types explicitly. A CSV can contain a comma inside a quoted field, so first check the preview before attempting to split rows manually. Review the import steps in order. If the automatic type conversion removed leading zeros, adjust the type step before loading the table.

Add a second file exported with the same headers. Keep a consistent naming convention for monthly inputs and prevent unrelated copies from being imported. Choose explicitly whether the incoming files are full snapshots or incremental additions. Appending a full August snapshot to another full August snapshot can double the totals. As a practical test, place an identical copy alongside the original and see how your row count and duplicate check respond.

Try the refresh after temporarily renaming the source directory in your practice copy. The failure shows which query depends on the folder. Update the source setting and check the resulting dates, row counts and totals after bringing the source back. Record the file name, import date and documented exceptions so another learner can repeat the import. Keep only synthetic records in a workbook you intend to share as a learning sample.

Identify which numbers must agree

On Checks, compare the count of incoming records with accepted rows and rejected rows. For example, with 25 incoming lines, 23 accepted and two rejected, the line count reconciles. Name each rejection so it can be corrected or signed off. Reconcile working net sales against an independently calculated total for the accepted source lines. A small variance might reflect rounding at a different stage. A large one can indicate duplicated records, the wrong month or an omitted category.

Build a separate check for discounts. For the five sample lines, gross sales before discounts are ₹1,500 and the recorded discount reduces sales by ₹50. Calculate gross sales from quantity and unit price and compare gross sales minus recorded discounts with net sales. Then change the cable discount from 10% to 5% and predict the effect before recalculating. The discount drops to ₹25 and net sales rise to ₹1,475. This check helps you trace an unexpected month total to quantity, price or discount before inspecting every transaction.

As a fault-finding exercise, copy the sample, change the A103 price from ₹400 to ₹40 and refresh the report. You should see a difference of ₹360 compared with the unchanged accepted source. Then repair the price, introduce an unknown SKU and repeat. A well-built check surfaces the invalid category while keeping the missing value visible for review. Test one fault at a time so you know which check caught it.

Complete a monthly sales workbook for your portfolio

Expand the example to two months and at least 25 lines. Include a deliberate duplicate, two products in the same invoice, two dates near the period boundary and a returned product. Agree on the sign rule for returns before calculating anything. For instance, a credit note represented by a negative quantity should reduce net sales. If your main sales input accepts only positive quantities, add a separate transaction type or return workflow and document how the report includes it.

  1. Save the original, create the working table and write the rule for one record per invoice line. Decide what the finished report should answer.
  2. Clean the dates and keys, repair or flag invalid entries, and build formulas for discounts and net sales. Check the formulas on example rows before filling the full table.
  3. Create a category summary, a PivotTable cross-check and a clear chart. Document how to pick the month and refresh the report.
  4. Ask another learner to refresh the workbook using a new sample file. Collect unclear instructions and confirm the ending row count and sales total.

Submit the workbook, the fictional raw file, a one-page readme and a PDF showing the selected month. In the readme, explain the month filter, how returns were counted, and where to look for rejected rows. Include one note about a decision you made when the data was ambiguous. Excel skill is visible in how clearly you make the work traceable and easy to repeat.

Prepare for a practical Excel interview

Explain choices while working on a timed exercise

Practise with an unfamiliar 20-row spreadsheet. Give yourself two minutes to read the field headers, names and requested outputs. State the record grain in a short note and inspect data types. Finish the essential cleaning and reporting before taking time to decorate the workbook. Build one independent check, such as comparing a copied PivotTable total with a formula summary. Explain in a sentence how you would repeat the process next month.

For the question “How do you find a broken lookup?”, start by checking the lookup value, the matching table and whether both values use the same type. Show a leading zero, a duplicate key and an extra space. For “What would you check before sending a report?”, demonstrate the date filter, pending exceptions, totals, refresh status and headers. Practice speaking through a discrepancy without immediately hiding it behind error handling.

If asked to improve a slow workbook, describe what you would measure first. Time the refresh or calculation on a copy with representative data. Inspect repeated formulas that scan large ranges, imports that pull unnecessary columns and sheets that calculate the same total many times. Apply one change, then validate the numbers against the unchanged copy before reporting improvement. A simpler design can be easier for the team to maintain; explain how you would choose between worksheet formulas and an import workflow for the task.

Frequently asked questions about Advanced Excel in Ambala

Should I take Advanced Excel or the MS Office course?

Advanced Excel is a good fit when you want more time on spreadsheet cleanup, lookups, PivotTables, controls and reporting. Choose MS Office with Advanced Excel when you also need Word and PowerPoint and want to combine all three in one workplace assignment. Bring one sample type of work you hope to do, such as inventory tracking or meeting documents, when asking about a batch.

Can I practise if my Excel version lacks XLOOKUP?

Yes. Learn a working exact-match method such as INDEX with MATCH or VLOOKUP for a suitable table. The underlying tasks of choosing a stable key, matching types, checking duplicate keys and confirming totals apply across versions. Before practising Power Query or newer functions, check the edition and device available to you and ask which methods the batch uses.

What should I do if my figures disagree with the source report?

Save a copy and record the difference. Compare the periods, which returns or discounts are included, and whether each number represents a line or an invoice. Then reconcile one category or day at a time until you find the rows causing the variance. Keeping a brief record of the cause helps you recognise it on the next refresh and explain the correction to the recipient.

What can I ask before visiting the Ambala centre?

Ask about the next batch, current fees, the Excel version used for practice and the reporting exercises you will complete. If you need preparation for an office or MIS interview, bring a sample job description and ask which skills are covered in class. You can also ask how feedback on workbook exercises is given and what to revise before your first lesson.

Course syllabus

  1. Useful formulas and functions

    Use relative and absolute references, conditional formulas and lookups to answer questions about a dataset.

  2. Data sorting, filtering and validation

    Sort and filter clean tables, check duplicate values and use data validation on input fields.

  3. Charts and presentation-ready reports

    Use a chart or PivotTable to summarise results and explain what the numbers show.

  4. Practical spreadsheet exercises

    Build a repeatable report and check its totals against the original data.

What you’ll be able to do

Turn a raw worksheet into a checked report with formulas, summaries and a chart.

Example practice project

Build a monthly sales report

Clean a sample table, calculate totals by category, and present key results with a chart or pivot table.

Prepare to discuss your project in an interview

Practise describing what you built, one choice you made and how you checked the result.

Question to practise
How did you check that your document, report or data entry was accurate?

See the interview practice guide →

Before you join

Should I take MS Office first?

If you are new to spreadsheets, start with MS Office. This course is a better fit once you can enter data and write basic formulas.