Excel essentials

In short

A spreadsheet stores data in a grid of cells and recalculates every dependent formula the moment a value changes, which is why Microsoft Excel remains the default reporting, costing and MIS tool in almost every Indian office. "Excel essentials" covers the parts every fresher is expected to use on day one: the workbook structure, correct data entry, cell referencing, the core aggregate/text/date functions, formatting, sorting and filtering, data validation, charts and printing. It is examined in university papers such as "IT Tools and Business Systems" / "Computer Applications" under UGC CBCS, in NIELIT CCC and O-Level, and in the AICTE/NSQF employability-skills modules. In placements it appears as a timed hands-on test (clean a messy sheet, build a summary, make a chart) at consultancies, banks, BPO/KPO, analytics and ops roles — Deloitte, KPMG, EY, TCS, Infosys, Wipro, Accenture, HDFC/ICICI, Flipkart, Amazon, Zomato and most manufacturing MIS teams.

Explained

The workbook, the grid and how Excel stores a value

• A workbook is the file; it holds worksheets (tabs); each sheet is a grid of cells addressed by column letter and row number (B7). A rectangular block is a range (B2:D10); a whole column is B:B.
• Grid limits: 1,048,576 rows × 16,384 columns (A to XFD) per sheet; up to 32,767 characters in one cell; number of sheets limited only by memory.
• Every cell has four layers: the stored value, the formula (if any), the number format (how it is displayed) and the style/border. Changing the format never changes the stored value — this is the single most useful idea in Excel.
• Four data types: number, text, logical (TRUE/FALSE) and error. Dates and times are *numbers*, currency and percentage are *numbers with a format*.
• Default alignment is the free diagnostic: numbers and dates sit right, text sits left. A "number" that hugs the left edge is text and will not add up.
• Dates: stored as a serial count of days from 1 January 1900 = 1 (so 1-Jan-2000 = 36526). Times are the fractional part: 0.5 = 12:00 noon. Excel deliberately keeps a bug from Lotus 1-2-3 — it accepts 29-Feb-1900 — so serials before 1-Mar-1900 are off by one. Mac files may use the 1904 system; mixing the two shifts all dates by 1,462 days.
• Regional setting matters in India: Windows set to English (India) reads 05-10-2026 as 5 October; a US locale reads it as 10 May. Always check before importing.
• Excel stores numbers in IEEE double precision but keeps only 15 significant digits, so =(43.1-43.2)+1 shows 0.899999999999999 instead of 0.9, and any identifier longer than 15 digits — a 16-digit card number, for instance — loses its last digits. Card numbers, account numbers, Aadhaar and IMEI must all be entered as text.

Entering, editing and cleaning data

• Type → Enter (moves down) or Tab (moves right); F2 or double-click to edit in place; Esc cancels; Ctrl+Z undoes.
• AutoFill: drag the fill handle to copy; drag with a pattern (Jan, Feb… / 1, 2…) to extend a series; double-click the handle to fill down to the end of the adjacent data; Ctrl+D / Ctrl+R fill down/right.
• Fill → Series gives linear or growth series with a step and stop value; custom lists (department names, branch order) are defined in File → Options → Advanced.
• Flash Fill (Ctrl+E): type one or two examples of the result (e.g. first name from a full name) and Excel infers the pattern. Fast but static — it does not update if the source changes.
• Paste Special (Alt+E+S): paste Values (kills formulas), Formats, Formulas, Transpose (rows↔columns), Column widths, Multiply/Add (e.g. multiply a column by 1 to convert text-numbers), or Paste Link.
• Text to Columns: split a column by delimiter (comma, space, pipe) or fixed width; also the standard cure for text-stored numbers and dates (choose the correct Date format in step 3).
• Cleaning tools: TRIM (extra spaces), CLEAN, Find & Replace (Ctrl+H) with Match case / Entire cell options, Remove Duplicates (Data tab — it deletes rows, so copy the sheet first), Go To Special (Ctrl+G → Special) to select blanks, constants, formulas or visible cells only.
• Delete vs Clear: Delete key clears contents only; Home → Clear → All also removes formats and comments; right-click → Delete removes the cell and shifts neighbours, which can break formulas.

Formulas, operators and cell references

• Every formula begins with =. It may contain constants, cell references, operators, functions and names. Ctrl+` (Show Formulas) displays all formulas at once.
• Operator precedence: % → ^ → * / → + - → & (text join) → comparisons (= <> > < >= <=). Brackets override; =2+3*4 is 14, =(2+3)*4 is 20.
• Reference types (toggle with F4 while editing):
  • Relative B2 — shifts when copied. Use for row-wise calculations.
  • Absolute $B$2 — never shifts. Use for a single rate, factor or total.
  • Mixed $B2 (column locked) or B$2 (row locked). Essential for a copy-across-and-down grid such as a multiplication or rate matrix.
• Ranges and operators: : range, , union (=SUM(A1:A5,C1:C5)), space intersection; Sheet2!A1 for another sheet, '[Budget.xlsx]Apr'!A1 for another workbook.
• Named ranges: select a range → Name Box → type GST_Rate. Names are absolute by default, self-documenting (=Qty*Rate*(1+GST_Rate)), scoped to sheet or workbook, and managed in Formulas → Name Manager. Names cannot look like a cell address and cannot contain spaces.
• Calculation mode (Formulas → Calculation Options): Automatic by default; switch to Manual on very large sheets and press F9 to recalculate. A sheet left on Manual showing stale numbers is a classic reporting error.
• Auditing: Trace Precedents / Dependents, Evaluate Formula (step through), Watch Window, and the green triangle warnings for inconsistent formulas or numbers stored as text.
• Circular reference: a formula that refers to its own cell, directly or through a chain; Excel warns and returns 0 until it is fixed.

Core functions a fresher must know

• Aggregates: SUM, AVERAGE, MIN, MAX, MEDIAN, MODE.SNGL, PRODUCT, SUMPRODUCT (sum of paired products — weighted averages), STDEV.S / STDEV.P.
• Counting: COUNT (numbers only), COUNTA (any non-empty cell), COUNTBLANK. Text and blanks are ignored by SUM and AVERAGE; a blank is skipped but a zero is averaged in — they are not the same.
• Ranking and position: LARGE(range,k), SMALL(range,k), RANK.EQ, PERCENTILE.INC.
• Rounding: ROUND(x,n) (half away from zero), ROUNDUP, ROUNDDOWN, MROUND (to nearest multiple), CEILING.MATH, FLOOR.MATH, INT (down to integer), TRUNC (chops digits), ABS, MOD. Also SQRT, POWER, EXP, LN, LOG10.
• Text: LEN, LEFT, RIGHT, MID, FIND (case-sensitive) / SEARCH (not), SUBSTITUTE, REPLACE, UPPER, LOWER, PROPER, TRIM, CONCAT / TEXTJOIN(sep,TRUE,range), &, TEXT(value,"format"), VALUE.
• Date and time: TODAY(), NOW(), DATE(y,m,d), DAY, MONTH, YEAR, EOMONTH, EDATE, WEEKDAY, NETWORKDAYS (working days, with a holiday list), DATEDIF(start,end,"Y"/"M"/"D") for age and tenure. Subtracting two dates gives days; format the answer as General, not Date.
• Information: ISBLANK, ISNUMBER, ISTEXT, ISERROR, N, T.
• Dynamic-array functions (SORT, UNIQUE, FILTER, SEQUENCE) exist only in Microsoft 365 / Excel 2021 and later; they return a spilled range. Older versions (2016/2019) do not have them, so write for the version the office actually runs.

Formatting and number formats

• Ctrl+1 opens Format Cells: Number, Alignment, Font, Border, Fill, Protection. Format Painter copies formatting; cell styles standardise a report.
• Built-in number formats: General, Number (decimals, thousands separator, negatives in red/brackets), Currency vs Accounting (Accounting aligns the ₹ symbol and the decimal point and shows zero as a dash), Percentage (displays the stored value × 100 with a % sign — store 0.18 to show 18%; applying the % format to a cell that *already holds* 18 shows 1800%, while typing 18 into an already-percent-formatted cell is read as 18%), Date, Time, Fraction, Scientific, Text, Special.
• Custom format has up to four sections: positive;negative;zero;text. Codes: 0 forced digit, # optional digit, ? aligning space, , thousands (a trailing comma scales by 1,000), "text" literal, [Red] colour, [>=1000] condition. Example: #,##0.00;[Red](#,##0.00);"-";@.
• Indian digit grouping (lakh/crore) is not the default #,##0 grouping. Recent Microsoft 365 builds include an Indian format; otherwise use a conditional custom format of this shape: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0 — giving 1,23,45,678 instead of 12,345,678. For scaled reporting, a trailing comma divides by 1,000 (#,##0,," Mn" shows millions); a crore is 10⁷, which no whole number of commas gives, so divide by 10000000 in the formula and label the column "₹ crore".
• Alignment and layout: Wrap Text, Shrink to Fit, Merge & Center (avoid in data tables — it breaks sorting, filtering and selection; use Center Across Selection instead), indent, text rotation.
• Conditional formatting (Home tab) applies a format only when a rule is true: Highlight Cell Rules (greater than, between, text contains, duplicate values), Top/Bottom Rules, Data Bars, Colour Scales, Icon Sets, and "Use a formula" for anything else (e.g. =$E2>$F2 applied to $A2:$H2 to shade the whole row). Rules are evaluated in order in Manage Rules, with a Stop If True option.

Managing data: sort, filter, tables, validation, views

• Data for analysis must be a flat list: one header row, one record per row, one fact per column, no blank rows/columns, no merged cells, no sub-totals inside the data.
• Sort (Data → Sort) allows multiple levels, sort by cell colour or by a custom list (e.g. Grade A/B/C), and case-sensitive option. Always sort the whole table, never a single column.
• AutoFilter (Ctrl+Shift+L) gives per-column dropdowns with value checkboxes, text/number/date filters and search. SUBTOTAL(109,range) sums only visible rows; AGGREGATE can also ignore errors. Advanced Filter uses a criteria range and can copy unique records elsewhere.
• Excel Table (Ctrl+T): converts a range into a named object with banded rows, a filter row, auto-expansion when you add rows, a Total row, and structured references (=SUM(Sales[Amount])) that stay readable. Tables are the recommended base for charts and summaries.
• Data Validation (Data tab): restrict a cell to a whole number between limits, a date range, a text length, or a List (dropdown from a range or typed items); add an Input Message and a Stop/Warning Error Alert; Circle Invalid Data finds existing violations. Validation only checks typed entries — pasted values can bypass it.
• Views: Freeze Panes (freeze top row / first column / at the cursor), Split, Hide/Unhide rows, Group & Outline with collapsible levels, Zoom, New Window + Arrange All to see two sheets of one file together.
• Protection: all cells are "Locked" by default but locking only bites once you Protect Sheet — so unlock the input cells (Ctrl+1 → Protection) *before* protecting. Protect Workbook guards sheet structure; File → Info → Protect Workbook → Encrypt with Password protects the file itself. Sheet/structure passwords are weak and easily removed — they prevent accidents, not attackers.

Charts, printing and file formats

• Pick the chart from the question, not the ribbon: Column/Bar for comparing categories, Line for a trend over equally spaced time, Scatter (XY) for the relation between two numeric variables and for unequal x-spacing, Pie/Doughnut only for 3–6 parts of one whole, Combo with a secondary axis when two series have different units (sales in ₹ crore and growth in %), Sparklines for in-cell trends.
• Chart anatomy: data series, category (x) axis, value (y) axis, legend, data labels, gridlines, title, axis titles, trendline with R². Switch Row/Column flips the series; Select Data edits ranges.
• Printing: Page Layout → Orientation, Margins, Print Area, Print Titles (repeat header rows on every page), Scale to Fit (1 page wide × N tall), Page Break Preview, headers/footers with page numbers, Print Gridlines, and Print Selection/Active Sheet/Entire Workbook.
• Keyboard shortcuts worth memorising: Ctrl+Arrow jump to edge, Ctrl+Shift+Arrow select to edge, Ctrl+Home to A1, Ctrl+A, Alt+= AutoSum, Ctrl+; today's date, Ctrl+Shift+; time, Alt+Enter line break in a cell, Ctrl+Enter fill selection, F4 toggle $ / repeat last action, Ctrl+Page Up/Down change sheet, Shift+F11 new sheet, Ctrl+9/Ctrl+0 hide row/column, Ctrl+Shift+1 number format, Ctrl+K hyperlink.

• A .csv loses formulas, formatting, charts and extra sheets, and mangles leading zeros (PIN code 560001 survives, 0612345 does not) and dates. Re-saving a .csv repeatedly is how data quietly degrades.

Key points

• Cell - intersection of a column and a row; the address B7 is column B, row 7.
• Grid limit - 1,048,576 rows × 16,384 columns (A…XFD) in one worksheet.
• Value vs format - formatting changes only the display; the stored number is unchanged.
• Alignment test - numbers/dates align right by default; left-aligned "numbers" are text and will not sum.
• Date serial - dates are day counts from 1-Jan-1900 = 1; time is the decimal fraction of a day.
• Relative reference - B2 shifts when the formula is copied.
• Absolute reference - $B$2 stays fixed; mixed $B2 / B$2 locks only column or only row. F4 cycles them.
• Precedence - %, ^, * /, + -, &, then comparisons; brackets override.
• COUNT vs COUNTA - COUNT counts numbers, COUNTA counts any non-empty cell including text.
• ROUND vs display rounding - ROUND changes the value; decimal formatting only hides digits, so totals can appear "wrong by one paisa".
• Percentage format - displays the stored value × 100; store 0.18, display 18%.
• Indian grouping - lakh/crore style (1,23,45,678) needs a conditional custom number format in most versions.
• Conditional formatting - formats a cell only when a rule is TRUE; formula rules must anchor the column (=$E2>$F2).
• Data Validation - restricts what can be typed, including dropdown lists; pasting can bypass it.
• Excel Table (Ctrl+T) - auto-expanding named range with structured references; the right base for charts and summaries.
• Freeze Panes - locks header rows/columns on screen while scrolling (does not affect printing — use Print Titles for that).
• SUBTOTAL(109,…) - sums only the rows left visible by a filter.
• Merged cells - break sorting, filtering and selection; avoid inside data tables.
• Protect Sheet - locking matters only after protection; unlock input cells first. It is accident prevention, not security.

Formulas and examples

Example 1 — marks sheet (aggregates and rounding). Marks in B2:F2 are 78, 65, 90, 52, 84 out of 100 each.

• =SUM(B2:F2) → 78+65 = 143, +90 = 233, +52 = 285, +84 = 369.
• =AVERAGE(B2:F2) → 369 / 5 = 73.8.
• =G2/500 formatted as Percentage → 369/500 = 0.738 = 73.8%.
• =MAX(B2:F2) = 90, =MIN(B2:F2) = 52, =COUNT(B2:F2) = 5.
• =ROUND(73.8,0) = 74; =ROUNDDOWN(73.8,0) = 73. If one subject were blank, AVERAGE would divide by 4 (giving a higher mean), while treating it as 0 requires entering 0.

**Example 2 — why $ matters (increment sheet). Hike rate 8% is in B1**. Basic pay in A4:A6 = 25,000 / 32,000 / 41,500.

• In B4 write =A4*(1+$B$1) and copy down: 25,000 × 1.08 = ₹27,000; 32,000 × 1.08 = ₹34,560; 41,500 × 1.08 = ₹44,820.
• With =A4*(1+B1) instead, the copies become A5*(1+B2) and A6*(1+B3). B2 and B3 are empty, so the results collapse to the plain basic pay — ₹32,000 and ₹41,500, silently wrong with no error message. This is the most common deduction in a practical exam.

Example 3 — GST invoice and rounding (CGST/SGST split). Taxable value ₹18,640, GST 18%.

• Total tax =ROUND(18640*18%,2) → 18,640 × 0.18 = ₹3,355.20; invoice total = 18,640 + 3,355.20 = ₹21,995.20.
• Intra-state split: CGST 9% = SGST 9% = =ROUND(18640*9%,2) = ₹1,677.60 each (sum ₹3,355.20 ✓).
• Reverse calculation from an inclusive price of ₹2,360 at 18%: taxable =2360/1.18 = ₹2,000, GST = ₹360.
• Note: if you only *format* 3,355.20 to zero decimals it still holds 3,355.2 and column totals will differ from the printed figures by a few paise — use ROUND when the rupee figure is the legal one.

Example 4 — due date and ageing. Invoice date in A2 = 05-10-2026, credit period 45 days in B2.

• Due date =A2+B2: October has 31 days, so 26 days reach 31-Oct and the remaining 19 fall in November → 19-Nov-2026.
• Days outstanding as on 29-10-2026: =TODAY()-A2 = 24 days (format General, not Date, else it shows 24-Jan-1900).
• =IF is not needed to see it is not yet overdue: due − today = 19-Nov − 29-Oct = 21 days to go.
• =EOMONTH(A2,0) = 31-Oct-2026; =EOMONTH(A2,1) = 30-Nov-2026 — handy for monthly MIS cut-offs.

Example 5 — CGPA by weighted average (SUMPRODUCT). Credits in B2:B5 = 4, 3, 3, 2; grade points in C2:C5 = 9, 8, 10, 7.

• =SUMPRODUCT(B2:B5,C2:C5) → (4×9) + (3×8) + (3×10) + (2×7) = 36 + 24 + 30 + 14 = 104.
• =SUM(B2:B5) = 4+3+3+2 = 12.
• CGPA =104/12 = 8.6667 → =ROUND(8.6667,2) = 8.67.
• A plain =AVERAGE(C2:C5) gives (9+8+10+7)/4 = 8.50 — wrong, because it ignores credits.

Where it is used

• MIS and analyst roles across Indian IT and services: daily/weekly dashboards, utilisation and headcount trackers, SLA reports built on filtered tables and charts.
• Finance and accounts: Tally/SAP exports cleaned in Excel for GSTR-1 vs GSTR-2B reconciliation, TDS working under the Income-tax Act, bank reconciliation, depreciation and costing sheets; ROUND discipline matters because the rupee figure is filed.
• HR and payroll: salary registers (basic/HRA/PF/ESI), attendance from biometric dumps, DATEDIF for tenure and notice periods, data validation for designation and location lists.
• Banking, NBFC and insurance operations: branch-wise collection sheets, loan ageing buckets, conditional formatting to flag overdue accounts; most back-office tests at HDFC, ICICI, Axis and Bajaj Finserv include a spreadsheet round.
• Supply chain, retail and manufacturing: stock registers, reorder-level sheets, production and QC logs, vendor rate comparisons, print-ready shop-floor formats using Print Titles and Fit to Page.
• Government and campus contexts: NIELIT CCC/O-Level practicals, state e-governance data entry, college marks and attendance sheets, NAAC/AISHE data compilations.

Interview questions

• Q: What is the difference between a relative and an absolute cell reference? A: A relative reference like B2 shifts when the formula is copied; an absolute reference $B$2 always points to the same cell. F4 toggles between relative, absolute and the two mixed forms.
• Q: Why does my date suddenly show as 45935? A: Dates are stored as day serials from 1-Jan-1900; the cell's format was changed to General or Number, so the underlying number is displayed. Re-apply a Date format.
• Q: A column of numbers imported from a system won't add up. Why? A: They are stored as text — visible from the left alignment or a green triangle. Fix with Text to Columns, VALUE, or Paste Special → Multiply by 1.
• Q: Difference between COUNT, COUNTA and COUNTBLANK? A: COUNT counts numeric cells, COUNTA counts all non-empty cells including text and errors, COUNTBLANK counts empty cells.
• Q: Is rounding by number format the same as the ROUND function? A: No. Formatting only hides decimals while the full value is still used in calculations; ROUND actually changes the stored value, which is what you need for rupee amounts on an invoice.
• Q: What does an Excel Table (Ctrl+T) give you over a plain range? A: Auto-expansion when rows are added, a filter row and total row, banded formatting, and readable structured references like Sales[Amount] that charts and formulas pick up automatically.
• Q: What is Freeze Panes, and will it repeat headers when printing? A: It locks chosen rows/columns on screen while scrolling; printing needs Page Layout → Print Titles to repeat header rows on every page.
• Q: How do you make a dropdown list in a cell? A: Data → Data Validation → Allow: List, then give a source range or comma-separated items; add an input message and error alert. Note that pasting can still bypass validation.
• Q: Why are merged cells discouraged? A: They break sorting, filtering, selection of full columns and many formulas; use Center Across Selection for the same look without the damage.
• Q: What do #REF! and #NAME? mean? A: #REF! means the formula points to a reference that no longer exists (a deleted row, column or sheet); #NAME? means Excel does not recognise a name — usually a misspelt function or an undefined named range.
• Q: How would you highlight an entire row where delivery date is later than due date? A: Select the data rows, Conditional Formatting → New Rule → Use a formula, enter =$E2>$F2 with the column letters anchored and the row relative, and set the fill.
• Q: CSV or XLSX for sharing data with another system? A: CSV for machine-to-machine data exchange, since it is plain text and portable — but it loses formulas, formats, multiple sheets and leading zeros; XLSX for any working file a person will use.
• Q: Can Excel handle a 20 lakh-row dataset? A: Not in one sheet — the limit is 1,048,576 rows. Use Power Query/Power Pivot, a database, or split the data; this is a standard follow-up on scalability.

Common mistakes

• Omitting the $ on a rate, total or lookup table, then copying the formula down — the answers shift silently and no error appears.
• Confusing display with value: formatting two decimals and then reporting a total that differs from the sum of the printed rows.
• Sorting or filtering only part of a table, so names and values no longer belong to the same record — irrecoverable once saved.
• Applying percentage format to a cell that already holds 18 (it becomes 1800%), or dividing by 100 twice when the format already scales the display.
• Leaving blank rows, merged cells or two header rows in the data, which breaks AutoFilter range detection and chart ranges.
• Treating AVERAGE of blanks as zero-inclusive, or averaging an already-averaged column instead of recomputing from totals.
• Hardcoding numbers inside formulas (=A2*0.18) instead of referring to a rate cell or named range, so a rate change needs every formula edited.
• Using a Line chart for unequally spaced numeric x-values (use Scatter), or a pie chart for ten-plus categories.
• Relying on sheet protection or a workbook password as real security, and losing the only copy by overwriting a .xlsx as .csv.

Related topics

Excel formulas: lookup, logic and pivot tables · Word, Google Docs and documentation · PowerPoint and presentation design · Email, calendar and productivity tools · Professional ethics, data privacy and security at work · Personal finance basics for freshers