Excel in 60 Minutes
What You Must Know
A curated, deeply explained guide of every trick, formula, function and technique covered in the 1-hour MS Excel Job Readiness Workshop — with real examples.
The fastest way to look like an Excel pro. These shortcuts save hours every week in a corporate job.
When you write a formula and copy it to other cells, Excel automatically adjusts the cell references. This is called a relative reference. But sometimes you want a reference to stay fixed — for example, if you have a tax rate in one cell that all rows should use. Press F4 while your cursor is inside a cell reference to add dollar signs ($) which lock it.
// Relative: changes when copied → bad for fixed values
=B2*C2
// Absolute: stays fixed when copied → use for constants
=B2*$E$1
// Mixed: row locked, column free (or vice versa)
=B2*$E1 ← column E locked, row floats
=B2*E$1 ← row 1 locked, column floats| A | B (Price) | C (Formula) | D (Result) |
|---|---|---|---|
| GST Rate → | $E$1 = 18% | ||
| Product 1 | ₹5,000 | =B3*$E$1 | ₹900 |
| Product 2 | ₹8,000 | =B4*$E$1 | ₹1,440 |
| Product 3 | ₹3,200 | =B5*$E$1 | ₹576 |
Without $E$1, copying the formula down would break it (E2, E3, E4...). The dollar signs keep it locked to the GST rate cell.
These 8 formulas cover 80% of what companies actually use in day-to-day Excel work.
IF is the backbone of decision-making in Excel. It asks a question — if the answer is YES it gives one result; if NO it gives another. You can also nest IFs to handle multiple conditions (but IFS function is cleaner for that).
=IF(logical_test, value_if_true, value_if_false)
Example — Pass/Fail based on marks:
=IF(B2>=40, "Pass", "Fail")
Nested IF — Grade system:
=IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C","D")))| A (Employee) | B (Sales ₹) | C (Formula) | D (Result) |
|---|---|---|---|
| Priya | ₹1,20,000 | =IF(B2>=100000,"Eligible","Not Eligible") | Eligible |
| Rahul | ₹75,000 | =IF(B3>=100000,"Eligible","Not Eligible") | Not Eligible |
Instead of nesting 4-5 IF functions (which becomes unreadable), IFS lets you list all conditions and results in a clean sequence. Available in Excel 2019 and above.
=IFS(condition1,result1, condition2,result2, ...)
Grade Calculator:
=IFS(B2>=90,"A", B2>=75,"B", B2>=60,"C", B2>=40,"D", TRUE,"F")
↑ TRUE at end acts as "else/default"SUMIF adds up values only where a condition is met. SUMIFS adds multiple conditions. This is the most-used formula in finance, accounts, sales MIS, and operations reports.
=SUMIF(range, criteria, sum_range)
=SUMIFS(sum_range, range1, criteria1, range2, criteria2)
Total sales by "Delhi" region:
=SUMIF(C2:C100, "Delhi", D2:D100)
Total sales in "Delhi" AND in "Q1":
=SUMIFS(D2:D100, C2:C100, "Delhi", E2:E100, "Q1")| A (Salesperson) | B (Region) | C (Sales ₹) | D (SUMIF Result) |
|---|---|---|---|
| Priya | Delhi | ₹50,000 | Delhi Total ₹1,10,000 =SUMIF(B:B,"Delhi",C:C) |
| Vikas | Delhi | ₹60,000 | |
| Anjali | Mumbai | ₹80,000 | Mumbai: ₹80,000 |
COUNTIF counts cells that match a condition. Use it for attendance tracking, lead counting, product stock checks, or any scenario where you need to count occurrences.
=COUNTIF(range, criteria)
Count students who scored above 75:
=COUNTIF(B2:B50, ">75")
Count "Present" in attendance list:
=COUNTIF(C2:C100, "Present")
Count cells containing "Delhi" text:
=COUNTIF(A2:A100, "*Delhi*")
↑ Asterisk (*) is a wildcard = "contains"Real-world data is messy. Extra spaces, inconsistent formatting, names split across columns — text functions fix all of this. Every data analyst uses these daily.
Remove extra spaces (most common data issue!):
=TRIM(A2)
Extract first 3 characters (e.g. city code from "DEL-001"):
=LEFT(A2, 3) → DEL
Extract last 3 characters:
=RIGHT(A2, 3) → 001
Extract from middle (start position, length):
=MID(A2, 5, 3) → 3 chars starting from position 5
Join text (two methods — same result):
=CONCATENATE(A2, " ", B2)
=A2 & " " & B2 ← "&" method is faster to type
Uppercase / Lowercase / Proper Case:
=UPPER(A2) =LOWER(A2) =PROPER(A2)| A (First) | B (Last) | C (Formula) | D (Full Name) |
|---|---|---|---|
| priya | sharma | =PROPER(A2&" "&B2) | Priya Sharma |
| rahul | verma | =PROPER(TRIM(A3)&" "&B3) | Rahul Verma |
Date calculations are used in HR (employee tenure), finance (invoice aging), logistics (delivery deadlines), and project tracking. Master these and you'll build reports no one else in your office can.
Today's date (updates automatically every day):
=TODAY()
Current date + time:
=NOW()
Number of years between two dates (employee tenure):
=DATEDIF(start_date, end_date, "Y")
=DATEDIF(B2, TODAY(), "Y") ← Employee age in years
Days between dates (invoice aging):
=TODAY()-B2 ← Days since invoice date
Last day of any month (for month-end reports):
=EOMONTH(TODAY(), 0) ← End of current month
=EOMONTH(TODAY(), 1) ← End of next monthThe single most asked-about Excel function in every job interview. Master this and you're immediately ahead of 70% of applicants.
VLOOKUP searches vertically down a column for a value you specify, then returns a value from the same row but from a column you choose. Think of it like a smart phone book — you look up a name and get the phone number.
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Arguments explained:
lookup_value = What you are searching FOR (e.g. Employee ID)
table_array = The full data range to search IN (e.g. A1:D100)
col_index_num = Which column number to RETURN (1=first, 2=second...)
FALSE = Exact match (always use FALSE / 0 in practice!)
Example — Find salary by Employee ID:
=VLOOKUP(G2, A2:D100, 3, FALSE)
↑ Search G2's value in column A, return column 3 (Salary)| A (Emp ID) | B (Name) | C (Salary ₹) | D (Dept) |
|---|---|---|---|
| E001 | Priya Sharma | ₹45,000 | Finance |
| E002 | Rahul Verma | ₹38,000 | IT |
| E003 | Anjali Patel | ₹52,000 | Sales |
Search for E002 → Formula: =VLOOKUP("E002",A2:D4,3,FALSE) → Returns ₹38,000
XLOOKUP is the modern replacement for VLOOKUP. It fixes every limitation — it can look left or right, handles errors gracefully, and the syntax is more intuitive. If your office uses Excel 365 or 2021, use XLOOKUP.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Same example as VLOOKUP — cleaner!
=XLOOKUP(G2, A2:A100, C2:C100, "Not Found")
↑ Search G2 in column A, return from column C
↑ If not found, show "Not Found" instead of ugly #N/A error
Look LEFT (impossible in VLOOKUP — easy in XLOOKUP):
=XLOOKUP(G2, C2:C100, A2:A100)
↑ Search by salary (C), return Employee ID (A) — left lookup!MATCH finds the position (row number) of a value. INDEX returns the value at a specific position. Combined, they replicate VLOOKUP but without any limitations — works for left lookups, multiple criteria, and in all Excel versions.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Find salary by Employee Name:
=INDEX(C2:C100, MATCH("Priya Sharma", B2:B100, 0))
Step-by-step logic:
1. MATCH finds ROW where "Priya Sharma" is in column B → returns 1
2. INDEX returns the value from row 1 of column C → ₹45,000Pivot Tables are the single most powerful feature in Excel. What takes hours in manual reporting takes seconds here.
A Pivot Table automatically groups, counts, sums, and compares your data without a single formula. You simply drag fields into four zones — Rows, Columns, Values, and Filters — and Excel does all the maths. It's the tool every MIS executive, sales manager, and finance analyst uses every single day.
Insert → PivotTable → New Worksheet → OK. A blank Pivot Table panel appears on a new sheet.Value Field Settings → choose Sum / Count / Average / Max / Min. Default is Sum for numbers.Refresh. The entire report updates instantly.PivotTable Analyze → Insert Slicer. Creates clickable filter buttons — makes your report look professional instantly.Raw Data (1000 rows of transactions) →
| Salesperson | Region | Product | Month | Sales ₹ |
|---|---|---|---|---|
| Priya | Delhi | Laptop | Jan | ₹55,000 |
| Rahul | Mumbai | Mobile | Jan | ₹28,000 |
| Anjali | Delhi | Tablet | Feb | ₹35,000 |
| ... 996 more rows ... | ||||
Pivot Output (auto-generated in seconds):
| Region (Rows) | Jan (Sum Sales) | Feb | Grand Total |
|---|---|---|---|
| Delhi | ₹55,000 | ₹35,000 | ₹90,000 |
| Mumbai | ₹28,000 | ₹42,000 | ₹70,000 |
| Grand Total | ₹83,000 | ₹77,000 | ₹1,60,000 |
This report that would take 2 hours manually was created by dragging Region → Rows, Month → Columns, Sales → Values. That's it.
Make your spreadsheet smart — colour cells automatically and prevent wrong data entry.
Conditional Formatting automatically changes how a cell looks based on its value — no manual colouring needed. It's used in dashboards, attendance sheets, sales trackers, and any report where you need to visually highlight patterns or outliers instantly.
C2:C100)Home → Conditional Formatting → Highlight Cell Rules / Top-Bottom Rules / Color Scales / Data Bars / Icon Sets| Use Case | Rule Type | Visual Result |
|---|---|---|
| Sales performance | Color Scale | 🟢 High → 🔴 Low automatically |
| Overdue invoices | Formula Rule | Red if TODAY()-date > 30 |
| Top 10 students | Top/Bottom Rules | Gold highlight on top 10% |
| Attendance sheet | Highlight Rules | Green=Present, Red=Absent |
| Budget vs Actual | Icon Sets | ▲ ▬ ▼ arrows auto-appear |
Data Validation restricts what can be entered in a cell. The most popular use is creating a drop-down list — like a form select box, but in Excel. It's used in HR forms, inventory systems, order trackers, and any shared spreadsheet where multiple people enter data.
D2:D100)Data → Data Validation → Allow: ListDelhi,Mumbai,Bangalore,Chennai (comma-separated) OR select a range of cells that contains your list valuesA chart communicates in 3 seconds what a table takes 3 minutes to understand.
| Chart Type | Use When | Best For |
|---|---|---|
| Bar / Column | Comparing categories | Sales by region, product comparison |
| Line Chart | Showing trends over time | Monthly revenue, stock price, growth |
| Pie / Donut | Showing parts of a whole | Market share, budget breakdown (max 5 slices) |
| Scatter Plot | Showing correlation | Price vs demand, age vs salary |
| Combo Chart | Two different metrics | Revenue (bar) + Growth % (line) on same chart |
Alt + F1 for instant chart on same sheet, or F11 for chart on new sheet.Change Chart Type → pick from gallery.Chart Design → Add Chart Element.Three power tricks that take under 30 seconds each but make colleagues think you've used Excel for years.
Flash Fill watches what you type in the first cell and automatically recognises the pattern — then fills the entire column in one keystroke. No formula needed. It's the fastest way to split, combine, reformat, or extract text from data.
| A (Original) | B (Type first, press Ctrl+E) | Result |
|---|---|---|
| priya.sharma@gmail.com | Priya Sharma | Priya Sharma ← extracted name! |
| 9876543210 | +91-98765-43210 | +91-98765-43210 ← reformatted! |
| Priya Sharma | PS | PS ← initials extracted! |
| 01-Jan-2026 | January 2026 | January 2026 ← reformatted date! |
When data is imported from systems or collected from forms, duplicate entries are common. Remove Duplicates instantly deletes all duplicate rows, keeping only unique values. It's used in customer lists, inventory data, and email lists before sending campaigns.
Data → Remove DuplicatesWhen your spreadsheet has thousands of rows, scrolling down means losing sight of the column headers. Freeze Panes locks your header row in place so it's always visible, no matter how far you scroll.
View → Freeze Panes → Freeze Panes