Excel for Freshers — From Spreadsheet to Shortlist

Week 1 — structure, types and formatting that means something. Most broken spreadsheets are broken here, before a single formula is written.

1. A sheet is a model, not a table

   One row per record, one column per field, a header row that never moves, and no blank rows in the middle. That is the whole rule, and it is what makes sorting, filtering and pivoting work later.

   The lesson

   A spreadsheet is a small model of a business: one row per record, one column per field, one header row, and the totals kept away from the data. Every function, pivot and chart in this course assumes that shape, so the sheet you build first is the one that makes the rest possible.

   Set the column order once — id, date, category, amount, notes — and never move it. Format dates as dates and amounts as numbers, which you can check by the fact that real numbers sit on the right of the cell by default. A number stored as text is invisible to SUM and to every pivot.

   The mistake is a merged header across three cells and a blank row between months. A merged cell breaks sorting and a blank row splits the data into two tables, so the pivot quietly ignores half the sheet. Colour the headers instead, and never leave a gap.

   SpreadsheetCopy

   id | date       | category | amount | notes
   1  | 2026-01-04 | travel   | 240.00 | auto
   2  | 2026-01-05 | food     | 180.00 |
   
   Rules: one header row . no blank rows . no merged cells .
          totals kept OUTSIDE the data .
          dates are dates and numbers are numbers.
   
   A number stored as text sits on the LEFT and is invisible to SUM.

   Example A sales sheet with a blank row between months sorts the months alphabetically and breaks every total below it. The blank row was a design choice, and it was the wrong one.

   Practice Take any sheet you already have and rewrite it as one clean block: single header row, no blanks, no merged cells in the data area, and one column per fact.

2. Formatting that carries meaning

   Number formats, freeze panes and conditional formatting are not decoration. They are how a reader knows a column is money, a date or a percentage without being told.

   The lesson

   Formatting in a work sheet is not decoration, it is communication: bold for a header, a thousand separator on money, one or two decimals on percentages, and a consistent date format. A reader should be able to tell a total from an input by looking, without a legend.

   Two habits do most of it. Freeze the top row and the first column so the headers stay visible while you scroll. And use conditional formatting for the one thing you need to see fast — amounts under a threshold in red, blank cells highlighted — rather than colouring rows by hand, which goes stale as the data changes.

   Three sets of numbers are worth knowing: number, currency and percentage. A percentage cell that shows 0.15 instead of 15% makes every reader stop and recalculate, and one cell formatted wrongly among hundreds is what gets a sheet distrusted.

   SpreadsheetCopy

   Freeze the header row and first column:
     View -> Freeze Panes -> Freeze Panes
   
   Conditional formatting for the one thing you must see fast:
     Amount < 100   -> red
     Blank cells    -> yellow fill
   
   Number formats:  #,##0   .   Rs#,##0   .   0.0%
   (0.15 shown as 0.15 and not 15% makes every reader recalculate.)

   Example Formatting 12000 as currency changes what the reader assumes; colour-scaling a margin column makes the bad rows find themselves. Merged cells, by contrast, break sorting and look identical in every screenshot on the internet.

   Practice Format one sheet properly: freeze the header row, set currency and percentage formats, and add a conditional format that highlights any value below target.

3. Absolute and relative references

   The dollar sign in $B$2 is the difference between a formula you write once and a formula you write forty times. This is the single most common gap in a fresher sheet.

   The lesson

   A reference without dollars — A2 — moves when you copy the formula down, which is what you want for rows. A reference with dollars — $A$2 — stays fixed, which is what you want for a rate, a target or a lookup table that lives in one place. Two characters decide which.

   Mixed references are the useful in-between: $A2 locks the column but lets the row move, and A$2 does the reverse. They are how a multiplication table is filled from one formula, and how a formula dragged across months keeps pointing at the same starting cell.

   The mistake is writing a formula without dollars, copying it down, and getting errors that look like missing data. Click the cell with the error, press F2, and watch which references moved — the wrong arrow is the whole bug, and it becomes obvious in two seconds.

   SpreadsheetCopy

   =A2*$B$1        the rate in B1 stays fixed as you copy down
   =B2*(1+$C$2)    the tax rate stays fixed
   =$A2            column locked, row moves
   =A$2            row locked, column moves
   
   Copy a formula without dollars and get errors?
   Click the cell, press F2, and watch which reference moved.

   Example A rate stored in $B$1 and referenced from C2 as =B2*$B$1 copies down correctly. Written as =B2*B1 it drifts to B2, B3, B4 and produces plausible wrong numbers — the worst kind of wrong.

   Practice Build a small invoice sheet with one tax rate cell, then copy the formula down twenty rows and check that every result still points at that one cell.

Weeks 2–3 — counting, deciding and looking things up. Twelve functions cover almost every office job; these are they.

1. Counting and adding on a condition

   COUNTIF counts rows that match something, SUMIF adds the ones that match, and both take a range, a condition and — for SUMIF — the range to add up. AVERAGEIF does the same for averages.

   The lesson

   SUMIF, COUNTIF, AVERAGEIF and their plural versions add and count only the rows that match: SUMIF(city, 'Pune', amount) totals Pune, COUNTIFS(city, 'Pune', type, 'remote') counts Pune remote jobs. They are how one sheet answers a hundred questions without a single pivot.

   The plural forms take pairs — range, condition, range, condition — and every pair is an AND. Text conditions go in quotes, comparisons go in quotes too ('>1000'), and a condition can point at another cell so the report becomes a small form: type a city, every number updates.

   The mistakes are a range that stops short of the last row, which is why tables are better than plain ranges, and a total row left inside the range being summed, which doubles your own figure. Check the range includes every row and excludes the total.

   SpreadsheetCopy

   =SUMIF(Table1[city], "Pune", Table1[amount])
   =COUNTIF(Table1[type], "remote")
   =AVERAGEIF(Table1[city], "Pune", Table1[amount])
   
   =COUNTIFS(Table1[city], "Pune", Table1[type], "remote")   -- AND
   
   =SUMIFS(Table1[amount],
           Table1[city], $H$1,          -- condition from a cell
           Table1[amount], ">1000")

   Example =SUMIF(B2:B500,"North",D2:D500) totals the North region in one line. Doing it by hand with a filter is the same answer and four minutes of your life every time the data changes.

   Practice From a 500-row sheet, produce a region-wise total, a count of orders above ₹10,000 and an average order value per region — with formulas, not filters.

2. IF and nested IF without the panic

   IF asks a yes/no question and returns one of two answers. Nesting is just putting another IF in the else position, and it stops being frightening once you write each branch on its own line.

   The lesson

   IF(condition, value_if_true, value_if_false) picks between two results. Nested inside each other, IFs grade, band and label: pass or fail, then first class or distinction. Read them from the inside out, like brackets, and they stop looking like a puzzle.

   Past three levels, switch to IFS(condition1, result1, condition2, result2, ...) where only the first true condition fires — no nesting at all. For a set of bands that will be maintained by somebody else, XLOOKUP with an approximate match against a small table is even better, because the bands live in cells rather than inside the formula.

   The mistake is a condition in the wrong order — a grade of 40 tested before one of 75 makes the higher band unreachable. Test the most restrictive condition first, and print the result on a couple of rows by hand to check before filling the column down.

   SpreadsheetCopy

   =IF(A2 >= 40, "Pass", "Fail")
   
   =IF(A2 >= 75, "Distinction",
      IF(A2 >= 40, "Pass", "Fail"))        read it from the inside out
   
   =IFS(A2 >= 75, "Distinction",
        A2 >= 40, "Pass",
        TRUE,     "Fail")                  first TRUE wins
   
   =XLOOKUP(A2, bands_min, bands_label, , -1)   -- approximate: bands in a table

   Example =IF(A2>=75,"Distinction",IF(A2>=60,"First",IF(A2>=40,"Pass","Fail"))) reads top-down, and the order of the tests is what makes it correct — the strictest condition has to come first.

   Practice Write a grade formula for one column, then add a second column that returns a comment like "needs revision" using IF and AND together.

3. Looking things up — XLOOKUP and INDEX-MATCH

   VLOOKUP only searches left to right and breaks when a column is inserted. XLOOKUP is the modern replacement in Microsoft 365, and INDEX-MATCH is the version that works everywhere, including older Excel and Google Sheets.

   The lesson

   A lookup fetches a value from another table by a key: give it an employee id, get the name. XLOOKUP(id, ids, names) is the modern form and reads left to right, with an optional fourth argument for the answer when nothing matches. INDEX and MATCH together do the same job in every version of Excel.

   Wrap it so a miss is readable: IFERROR(XLOOKUP(...), 'not found'). Lookups are also the honest way to bring data from a second sheet into a report — code in one sheet, descriptions in another — instead of retyping them, which is where hand-maintained spreadsheets go wrong.

   The mistake is VLOOKUP with a hard-coded column number, which breaks the moment somebody inserts a column between you and it. And a lookup key with a trailing space — "Pune " against "Pune" — returns nothing; TRIM both sides of the comparison.

   SpreadsheetCopy

   =XLOOKUP(A2, ids, names)                 by column NAME, not a number
   =XLOOKUP(A2, ids, names, "not found")    avoids #N/A
   =XLOOKUP(A2, ids, Table2, , , 1)         return several columns
   
   # Older Excel:
   =INDEX(names, MATCH(A2, ids, 0))
   
   # Never VLOOKUP with a hand-typed column number: it breaks on insert.

   Example =XLOOKUP(A2,Employees!A:A,Employees!C:C,"not found") returns the department for an employee code, and — unlike VLOOKUP — returns a readable message instead of #N/A when the code is missing.

   Practice Join two sheets: pull the department and manager for 200 employee codes, count how many codes did not match, and fix the mismatches before reporting anything.

Weeks 4–5 — cleaning, pivoting and presenting. This is the part of the course that turns into a portfolio piece.

1. Cleaning an export nobody tidied

   Real exports arrive with trailing spaces, duplicates, inconsistent capitals and dates stored as text. Cleaning is the first half of every analysis, not a chore before it.

   The lesson

   An export from a portal arrives with merged headers, blank columns, dates as text, lakhs written as "1.2 L", and duplicate rows. Cleaning it is the first hour of every real task, and saying so in an interview is not an admission — it is what the job is.

   Four moves clean almost anything. Remove blank rows and columns. TRIM and PROPER the text columns so " asha " and "ASHA" become "Asha". Split a combined column with "Text to Columns" or TEXTSPLIT. Then use "Remove Duplicates" on the id column, and count the rows before and after so you can report what you did.

   The mistake is editing the raw export and losing the original. Keep a "raw" sheet you never touch, do all the work in a second sheet, and your numbers stay auditable — which is the difference between a report a manager trusts and one they re-check by hand.

   SpreadsheetCopy

   =TRIM(A2)                       strip stray spaces
   =PROPER(TRIM(A2))               Fix Title Case
   =SUBSTITUTE(A2, "  ", " ")      collapse double spaces
   =IF(A2 = "", "missing", A2)     flag blanks
   =LEFT(A2, FIND(" ", A2) - 1)    first name
   =TEXT(B2, "yyyy-mm-dd")         force a date display
   
   Data -> Remove Duplicates . Text to Columns . Filter -> sort A-Z

   Example TRIM removes the extra spaces that make "North" and "North " count as two regions, Remove Duplicates drops the re-imported rows, and Text to Columns puts a mis-stored date back into a date.

   Practice Run all three on your own dataset, then write down how many rows you removed and what each removal was for — the note is what an interviewer asks about.

2. Pivot tables in fifteen minutes

   A pivot table groups rows by one field, summarises another and takes seconds. The skill is in choosing the rows, columns and values — that is the whole interface.

   The lesson

   A pivot table answers a question by dragging fields into four boxes: Rows (the categories you compare), Values (the numbers you add), Columns (the second breakdown), Filters (the slice). Ten thousand rows become a one-page answer in about four drags.

   Right-click a value and choose "Show values as" to turn a sum into a percentage of the total or a running total, and group a date field by Month to see a trend rather than a list of days. Set the number format once on the field and every figure in the table follows it.

   The mistake is a pivot built on a range that will grow tomorrow, which then misses the new rows. Select the data and press Ctrl+T first to make it a Table, build the pivot on the table, and new rows are included by a simple Refresh.

   SpreadsheetCopy

   Select the table -> Insert -> PivotTable
   
   Rows:    city
   Columns: month
   Values:  Count of id
   Filters: type
   
   Right-click a value -> Show values as -> % of Grand Total
   Group a date column: right-click -> Group -> Months
   Refresh after new data: right-click -> Refresh  (or Alt+F5)

   Example Rows = Region, Columns = Month, Values = Sum of Sales gives a month-by-month regional matrix. Dragging the same field into Filters gives you one pivot instead of twelve.

   Practice Build three pivots from one dataset: region by month, top ten products by revenue, and average order value per salesperson. Add a slicer to one of them.

3. The one-page dashboard

   A dashboard is not a wall of charts. It is three or four numbers that answer the question the reader came with, and one chart that shows the trend behind them.

   The lesson

   The deliverable is one page somebody can read in thirty seconds: the headline number at the top left, three or four supporting numbers beside it, and one chart underneath that shows the trend. Everything else — the raw data, the intermediate sums — lives on other sheets and is never printed.

   Build it from the pivot outputs so nothing is retyped, and add a title with the period and an "as of" date. Label units (₹, %, count) and one sentence under each chart saying what it shows. That sentence is what gets quoted in a meeting, and it is why the page survives being shared.

   The mistake is eight charts and every column of your working. A dashboard is a decision, not an archive. If a chart does not change what anybody would do, it belongs on a supporting sheet — and Ctrl+P is the test: if it does not fit on one page, it is not the dashboard.

   SpreadsheetCopy

   Tile 1  Total spend      =SUM(Table1[amount])
   Tile 2  This month       =SUMIFS(Table1[amount], Table1[month], "2026-01")
   Tile 3  Biggest category =INDEX(cats, MATCH(MAX(totals), totals, 0))
   Tile 4  vs last month    =IF(this>last, "UP", "DOWN") & TEXT(pct,"0%")
   
   Insert -> Sparkline for a 12-month trend.
   One page . a title . an "as of" date . units labelled.

   Example Big bold totals in the top row, a bar chart for comparison, a line for time, and nothing else. Every extra chart costs the reader attention and buys nothing.

   Practice Build a single screen that answers "how are we doing this quarter, and where is it weak?" using one pivot, three KPI cells and two charts. Print it to one page.

Structured tables, validation and formula-driven formatting, so the sheet stays right without you.

1. Structured tables and named ranges

   A plain range breaks the moment you add a row; a structured Table (Ctrl+T) grows by itself, and every formula that points at the table keeps including the new row.

   The lesson

   Names travel: =Table1[amount] says what it sums, whereas =SUM(B2:B50) says where to look. A reviewer six months later can read the first and not the second.

   Tables also carry the formatting and the filter, so you stop reapplying borders after every paste and the sheet stays tidy because you no longer touch it.

   SpreadsheetCopy

   Ctrl+T  ->  "My table has headers"  ->  Table Design -> name it Table1
   
   =SUM(Table1[amount])
   =COUNTA(Table1[id])
   =SUMIFS(Table1[amount], Table1[city], $H$1)
   
   # A plain range misses new rows; a table does not.

   Example =SUM(Table1[amount]) still works after you type a new row, while =SUM(B2:B50) silently misses row 51 - the bug that makes a report look right and be wrong.

   Practice Convert one of your sheets to a Table, rename it, and rewrite three formulas to use the table's column names.

2. Data validation and drop-downs

   Validation decides what may be typed into a cell: a whole number in a range, a date after today, or one of five values from a list. It is how a sheet stays usable by people who did not build it.

   The lesson

   A validation error at the moment of typing is worth an hour of cleaning later, and the input-message tab lets you write the rule in words where the person can see it.

   Keep validating in the import step too if the data arrives from elsewhere - cell validation is a courtesy to the typist, not a guard against bad data.

   SpreadsheetCopy

   Data -> Data Validation
     Allow: Whole number   Minimum: 0         -> refuses negative amounts
     Allow: List           Source: =Categories -> a drop-down
     Allow: Date           Start: =TODAY()     -> no past dates
   
   Custom: =AND(LEN(A2)=10, ISNUMBER(A2*1))    -> a 10-digit mobile number

   Example Data -> Data Validation -> List -> the five city names gives a drop-down; the same dialog refuses a past date with a simple rule.

   Practice Add a drop-down of five categories and a rule that refuses a negative amount, on one data-entry sheet.

3. Conditional formatting with formulas

   Built-in rules colour a cell below a threshold; a formula rule colours a whole row based on another column, which is what you want when a record is 'overdue' or 'unassigned'.

   The lesson

   The rule is a formula that returns TRUE or FALSE for the top-left cell, and Excel copies it down with the references shifted - which is why the $ matters.

   Keep the number of rules small. Five rules a reader understands beat fifteen that produce a rainbow nobody can decode.

   SpreadsheetCopy

   Select the table -> Home -> Conditional Formatting -> New Rule -> Use a formula
   
   = $F2 = "overdue"                    whole row red when status is overdue
   = ISBLANK($G2)                       whole row yellow when the owner is blank
   = AND($E2<TODAY(), $F2<>"closed")    overdue and still open

   Example Select the whole table and use =$F2="overdue" as the rule; the $ locks the column so each row is judged by its own status cell.

   Practice Highlight every whole row whose status says overdue, and every row whose owner is blank.

Power Query, what-if analysis and one macro, so the weekly report is built once and run forever.

1. Power Query: clean once, refresh forever

   Power Query records the steps you take to clean a file and repeats them on every refresh. Twenty minutes of weekly copy, paste and delete becomes one click.

   The lesson

   Every step shows in the Applied Steps list, so a change in the source means editing one step rather than redoing the cleaning by hand.

   The mistake is cleaning by hand "just this once"; that once becomes every week, and nobody else can reproduce what you did.

   SpreadsheetCopy

   Data -> Get Data -> From Text/CSV -> Transform Data
   
   Applied Steps (right pane):
     Source
     Promoted Headers
     Removed Blank Rows
     Changed Type  (date, amount)
     Trimmed Text  (city)
     Removed Duplicates
   
   Close & Load   ->   later, just click Refresh.

   Example Import a CSV, remove blank rows, set the date and amount types, and trim one text column; next month point it at the new file and press Refresh.

   Practice Run one messy CSV through five Power Query steps and refresh it after editing the source file.

2. What-if, Goal Seek and scenarios

   Goal Seek reverses a formula: you say what answer you need and it finds the input. Scenario Manager keeps several versions of the inputs side by side.

   The lesson

   This is the difference between reporting and deciding. Anyone can sum a month of sales; whoever can say what price hits the target is asked next time.

   Keep the inputs in their own block so Goal Seek and the scenarios have something stable to change, and so a reader can see exactly what you varied.

   SpreadsheetCopy

   Model:  Price(B1) * Units(B2) - Cost(B3) = Profit(B4)
   
   Data -> What-If Analysis -> Goal Seek
     Set cell:    B4 (profit)
     To value:    100000
     By changing: B1 (price)
   
   Data -> Scenario Manager -> Add: Optimistic, Conservative

   Example With profit as a formula of price and cost, Goal Seek asks what price gives a profit of one lakh and returns the number.

   Practice Build a small profit model, use Goal Seek to hit a target, and save two scenarios: optimistic and conservative.

3. LAMBDA and the weekly report you stop doing

   LAMBDA lets you name a formula you keep rewriting, and a macro records a sequence of clicks. Between them, a report that took an hour every Monday runs with one keystroke.

   The lesson

   Learn LAMBDA for the formulas you retype and a macro for the clicks you repeat. Reaching for the right one is what turns an hour into a minute.

   Save the workbook as .xlsm for macros, and remember they are disabled on someone else’s machine - the sheet should still make sense without them.

   SpreadsheetCopy

   =LAMBDA(rng, SUM(rng)/COUNT(rng))(B2:B31)          a reusable average
   
   =LAMBDA(city, SUMIFS(amount, cities, city))(H1)     a reusable sum-by
   
   View -> Macros -> Record Macro
     ... format, sort, export ...
   View -> Macros -> Macros -> Run   (or assign a shortcut)

   Example =LAMBDA(rng, SUM(rng)/COUNT(rng))(B2:B31) names an average and reuses it; a recorded macro formats, sorts and exports in one action.

   Practice Record a macro that cleans and formats one sheet, then run it on last week's copy.