Excel Formulas Every Working Professional Must Know in 2026 | Celoris

"Whether you are a fresh graduate applying for your first job, an accountant managing monthly MIS reports, or a business owner tracking sales data — Microsoft Excel remains the single most in-demand software skill in India's job market."

Yet most people use barely 10% of what Excel can actually do. This guide covers the most important Excel formulas and features that will genuinely change how you work — no fluff, no theory-only content. Just real formulas you will use on real data.

**💡 Pro Tip:** Every formula in this blog works in Excel 2016, Excel 2019, Microsoft 365, and Google Sheets (unless noted). Screenshots available in our live Excel course on celorisdesigns.com.

1VLOOKUP vs XLOOKUP

If you have been in any office environment in India, you have definitely heard someone say "bas VLOOKUP aata hai mujhe" as if that is the ultimate Excel flex. And while VLOOKUP is still useful, XLOOKUP has replaced it for good reason.

VLOOKUP — The Old Reliable

Search for a value in the leftmost column and return data from a column to the right.

=VLOOKUP(lookup_value, table_array, col_index, [range])

XLOOKUP — The Modern Standard

Fixes all VLOOKUP problems. Can look left, won't break on column inserts.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

**✅ Recommendation:** If you are on Microsoft 365, learn XLOOKUP as your primary lookup function. Still learn VLOOKUP for compatibility with older Excel files and colleagues who use older versions.

2IF, IFS, and Nested IF

The IF formula lets your spreadsheet make decisions automatically based on data.

Example: Automate Status

=IF(B2>=40, "Pass", "Fail")

For multiple grades (A, B, C, D), use IFS for much cleaner code:

=IFS(B2>=90,"A", B2>=75,"B", B2>=60,"C", B2>=40,"D", TRUE,"Fail")

3Conditional Calculations

SUMIF, COUNTIF, AVERAGEIF are game-changers. Instead of calculating totals for your entire dataset, you calculate only for rows that meet a specific condition.

SUMIF: Add only matching rows (e.g., Sales from Delhi: =SUMIF(B:B, "Delhi", D:D))
COUNTIF: Count matching rows (e.g., Transactions > ₹10,000: =COUNTIF(D:D, ">10000"))
SUMIFS: The upgraded version for multiple criteria (Delhi + March sales).

4TEXT Functions & Cleaning

Real-world data is always messy. Names have extra spaces, dates are in wrong formats. Excel's text functions allow you to fix everything instantly.

=TRIM()Removes extra spaces

=PROPER()Converts 'rAHUL' to 'Rahul'

=TEXTJOIN()Joins cells with separator

=TEXT()Formats dates/numbers as text

Pivot Tables: Data Analysis Superpower

A Pivot Table lets you instantly summarize thousands of rows of data into a compact, meaningful report — with zero formulas.

How to Create (Step-by-Step):

1. Click anywhere inside your data table
2. Go to Insert → PivotTable
3. Drag fields into Rows, Columns, and Values
4. Change Value Field Settings to SUM, COUNT, or AVERAGE

Dynamic Arrays (Next-Gen Excel)

Available in Microsoft 365, these functions "spill" results into multiple cells automatically.

=FILTER()Returns only rows matching a condition dynamically.
=UNIQUE()Extracts a unique list of values from a column.

Essential Shortcuts

Ctrl+Shift+L

Filter

Ctrl+T

Table

Alt + =

AutoSum

Repeat

Ctrl+1

Format

Ctrl+;

Date

Ctrl+D

Fill Down

Ctrl+Pgdwn

Sheets

Master Excel in 2026

Don't just use Excel — master it. Join our live batches and learn from verified trainers with real-world datasets.

Book Free Demo Today