Cellio Blog

Excel formulas, explained simply

Short, practical guides to the Excel and Google Sheets formulas people actually use at work: SUM to XLOOKUP, $ references, fixing errors, and how to pass an Excel test for a job.

Learn Excel

3 min read

Learn Excel Formulas for Beginners: A Step-by-Step Path

A beginner's path to Excel formulas: start with =, cell references and SUM, then AVERAGE, $ locks, IF and lookups. Clear order, real examples, no fluff.

3 min read

How to Learn Excel on Your iPhone (and Actually Remember It)

Yes, you can learn Excel on an iPhone. What works on a small screen, what doesn't, and a simple routine to build real formula skills in minutes a day.

3 min read

Best Apps to Learn Excel in 2026: What to Look For

Comparing the types of apps that teach Excel: video courses, reference apps and practice apps. What each is good for and how to pick the right one for you.

2 min read

Duolingo for Excel: Learning Formulas in Bite-Sized Lessons

Looking for a Duolingo for Excel? How short daily lessons, streaks and instant feedback work for spreadsheet formulas, and where the style falls short.

2 min read

How to Learn Excel for Free: A Practical Plan

You can learn Excel for free with Excel for the web, Google Sheets and free guides. Here's a plan that covers formulas, practice data and feedback.

2 min read

How Long Does It Take to Learn Excel? Realistic Timelines

How long it takes to learn Excel depends on the level you need. Realistic timelines for basic formulas, job-test level and advanced skills, with a daily plan.

3 min read

Excel Practice Exercises with Answers (Beginner to Intermediate)

20 Excel practice exercises with answers, from SUM and AVERAGE to $ references, IF, COUNTIF and VLOOKUP. Build the sheet in two minutes and test yourself.

3 min read

Learn Excel in 10 Minutes a Day: A 4-Week Plan

A 4-week plan to learn Excel in 10 minutes a day: the formulas to practise in order, from SUM to VLOOKUP, with mini tasks you can do anywhere.

3 min read

Excel Terms Explained: Cell, Range, Reference and More

Plain-English explanations of Excel terms beginners trip over: cell, range, reference, formula vs function, argument, fill handle, absolute reference and more.

3 min read

How to Practice Excel So It Actually Sticks

How to practice Excel effectively: write formulas from memory, use your own data, check answers by changing inputs, and space your practice. A simple method.

3 min read

Excel Formulas Cheat Sheet: 40 Formulas on One Page

An Excel formulas cheat sheet with the 40 formulas you'll use most: maths, conditions, lookups, text, dates and errors, each with a one-line example.

2 min read

Excel for Students: The Skills Your Courses Assume You Have

Excel skills students need for coursework, lab reports, group projects and internships: formulas, percentages, averages, charts and citing data cleanly.

2 min read

What Is Excel Used For? 12 Real Everyday Uses

What people actually use Excel for at work and at home: budgets, reports, tracking, analysis, planning and more, with the formulas behind each use.

Formulas

2 min read

Absolute Reference in Excel: What the $ Sign Does

Why formulas break when you fill them down, and how the $ sign fixes it. Absolute references explained with share-of-total, tax rate and lookup examples.

2 min read

Cell References in Excel: The Idea Every Formula Needs

What cell references are, why you point at cells instead of typing numbers, and how relative, absolute and mixed references behave when copied.

3 min read

Basic Excel Formulas: The 25 Everyone Should Know

The basic Excel formulas you'll use every week: totals, averages, counting, conditions, lookups, text and dates, with examples and full guides.

2 min read

SUM Function in Excel: Add a Column the Right Way

How to use the SUM function in Excel: add a range, several ranges, AutoSum, sum a whole column, and the mistakes that make totals wrong without warning.

2 min read

AVERAGE Function in Excel: Examples and Common Traps

How to use AVERAGE in Excel to find the typical value, why blanks and zeros change the answer, and when MEDIAN, AVERAGEIF or a weighted average is better.

2 min read

MIN and MAX in Excel: Find the Best and Worst Values

Use MIN and MAX in Excel to find the smallest and largest values, ignore zeros with MINIFS, find which row they're in, and cap values with MIN/MAX tricks.

2 min read

COUNT vs COUNTA in Excel (and COUNTBLANK): Which to Use

COUNT counts numbers, COUNTA counts anything that isn't empty, COUNTBLANK counts empty cells. Examples, differences and the mistakes that give odd counts.

2 min read

IF Function in Excel: Simple Examples You Can Copy

Learn the Excel IF function with simple examples: pass/fail, over budget, bonuses, text and number tests, and how to combine IF with AND, OR and other formulas.

2 min read

Nested IF in Excel: Multiple Conditions Without the Mess

How to write nested IF formulas in Excel for grades, tiers and commissions, why order matters, and when IFS or a lookup table is the cleaner choice.

1 min read

IFS Function in Excel: Many Conditions, One Clean Formula

How IFS works in Excel 2019, Microsoft 365 and Google Sheets: grades and tiers, the TRUE catch-all trick, and how it differs from nested IF.

1 min read

AND and OR in Excel: Combine Conditions in IF

Use AND and OR in Excel to test several conditions at once, inside IF or alone. Clear examples for bonuses, approvals and flags, plus NOT and common mistakes.

2 min read

COUNTIF in Excel: Count Cells That Match a Condition

How to use COUNTIF in Excel to count text matches, numbers above or below a value, blanks, partial matches and conditions stored in a cell.

1 min read

COUNTIFS in Excel: Count With Multiple Conditions

How COUNTIFS counts rows that meet several conditions at once: text and number criteria, date ranges, between two values, and how to do OR logic.

2 min read

SUMIF in Excel: Add Only the Rows That Match

How to use SUMIF in Excel to total sales by category, amounts above a threshold, or partial text matches, with examples and the argument-order trap explained.

2 min read

SUMIFS in Excel: Sum With Multiple Criteria (Examples)

How SUMIFS adds values that meet several conditions: category and month, date ranges, between two numbers, and building a summary table with locked references.

1 min read

AVERAGEIF and AVERAGEIFS in Excel: Average by Condition

Use AVERAGEIF and AVERAGEIFS in Excel to average only matching rows: by category, excluding zeros, between dates, with the #DIV/0! case handled cleanly.

2 min read

VLOOKUP in Excel: A Simple Guide With Examples

Learn VLOOKUP step by step: the four arguments, exact vs approximate match, locking the table with $, and fixing #N/A. Plus when to use XLOOKUP instead.

2 min read

VLOOKUP Not Working? 8 Causes of #N/A and How to Fix Them

VLOOKUP returning #N/A or the wrong value? The 8 most common causes, from extra spaces and text numbers to unlocked ranges, with a fix for each.

2 min read

XLOOKUP in Excel: The Easier Way to Look Things Up

How XLOOKUP works in Excel 365 and Google Sheets: basic lookups, built-in not-found text, looking left, several columns and last-match search.

2 min read

XLOOKUP vs VLOOKUP: Differences and Which to Use

XLOOKUP vs VLOOKUP side by side: syntax, default match type, looking left, inserted columns, speed and compatibility. A clear rule for which one to use.

2 min read

INDEX MATCH in Excel: Flexible Lookups Explained Simply

How INDEX and MATCH work together to look up values left or right, why it survives inserted columns, two-way lookups, and when XLOOKUP is the easier choice.

1 min read

MATCH Function in Excel: Find the Position of a Value

The MATCH function returns where a value sits in a list. Exact and approximate match types, checking if a value exists, and using MATCH inside other formulas.

1 min read

HLOOKUP in Excel: Look Up Values in Rows

HLOOKUP finds a value in the top row of a table and returns a value below it. Syntax, an example with months across, its limits, and modern alternatives.

1 min read

IFERROR in Excel: Replace Errors With Something Useful

Use IFERROR to replace #N/A, #DIV/0! and other errors with a blank, zero or message. Examples with lookups and division, and when hiding errors is a bad idea.

1 min read

TRIM Function in Excel: Remove Extra Spaces From Data

Use TRIM in Excel to remove leading, trailing and double spaces that break lookups and counts, plus the non-breaking space fix for web data.

1 min read

TEXT Function in Excel: Format Numbers and Dates in Formulas

Use the TEXT function to format numbers, currency, percentages and dates inside formulas, plus UPPER, LOWER and PROPER for changing text case.

1 min read

Combine Text in Excel: &, CONCAT and TEXTJOIN

Three ways to combine text in Excel: the & operator, CONCAT/CONCATENATE, and TEXTJOIN with separators that skip blanks. Examples for names, addresses and lists.

1 min read

LEFT, RIGHT and MID in Excel: Extract Part of a Cell

Use LEFT, RIGHT and MID with LEN and FIND to pull out codes, first names, area codes and file extensions from text. Clear examples for each function.

1 min read

ROUND in Excel: ROUND, ROUNDUP, ROUNDDOWN and Formatting

How ROUND, ROUNDUP and ROUNDDOWN work in Excel, rounding to tens or hundreds, MROUND for nearest 5, and why formatting decimals isn't the same as rounding.

1 min read

TODAY and NOW in Excel: Dates That Update Themselves

Use TODAY and NOW in Excel for days until a deadline, overdue flags, ages and timestamps. How dates work as numbers, and how to stop them updating.

1 min read

DATEDIF in Excel: Years, Months and Days Between Dates

Use DATEDIF to calculate complete years, months or days between two dates, ages and lengths of service, with all unit codes explained and known quirks.

1 min read

NETWORKDAYS in Excel: Count Working Days Between Dates

Count working days with NETWORKDAYS, exclude holidays, use custom weekends with NETWORKDAYS.INTL, and find a deadline with WORKDAY. Examples included.

2 min read

Relative vs Absolute References in Excel (With Examples)

Relative references move when you copy a formula; absolute references with $ stay fixed. A side-by-side comparison with examples of when to use each.

2 min read

Mixed References in Excel: $A1 vs A$1 Explained

Mixed references lock only the row or only the column. Learn when to use $A1 and A$1 with a multiplication grid, price tables and SUMIFS summary tables.

3 min read

Excel Formula Errors: #N/A, #VALUE!, #REF! and How to Fix Them

What each Excel error means (#DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #SPILL!) and the quickest fix, plus silent errors that show no warning.

1 min read

#DIV/0! Error in Excel: Why It Happens and 4 Ways to Fix It

The #DIV/0! error means a formula divides by zero or an empty cell. The causes, including the fill-down mistake, and four clean fixes with IF and IFERROR.

1 min read

Circular Reference in Excel: Find It and Fix It

A circular reference is a formula that refers to its own cell. How to find one in Excel, the usual causes like totals inside their range, and fixes.

1 min read

Order of Operations in Excel: Why Brackets Matter

Excel calculates in a fixed order: brackets, powers, multiply and divide, then add and subtract. See how it changes margin and percentage formulas.

1 min read

UNIQUE Function in Excel: Get a List of Distinct Values

Use UNIQUE in Excel 365 and Google Sheets to list distinct values, count them, sort them, and build automatic summary tables that grow with your data.

1 min read

FILTER Function in Excel: Return Rows That Match

Use FILTER in Excel 365 and Google Sheets to pull matching rows into a new table: one condition, AND and OR conditions, and a message when nothing matches.

1 min read

SORT and SORTBY in Excel: Sorted Lists That Update

Use SORT and SORTBY in Excel 365 and Google Sheets to create sorted copies of data that update automatically: by one column, descending, or several columns.

1 min read

SUMPRODUCT in Excel: Multiply and Add in One Step

Use SUMPRODUCT to total price × quantity without a helper column, calculate weighted averages, and count or sum with several conditions in any Excel version.

1 min read

MEDIAN in Excel: The Middle Value (and When to Use It)

How MEDIAN works in Excel, why it beats AVERAGE when data has outliers, median with conditions, and MODE for the most common value.

1 min read

RANK in Excel: Rank Scores, Sales and Leaderboards

Use RANK.EQ and RANK.AVG to rank values in Excel, highest or lowest first, break ties, and build a sorted leaderboard that updates automatically.

1 min read

LEN Function in Excel: Count Characters in a Cell

Use LEN to count characters in Excel, find hidden spaces, check code and ID lengths, count words, and limit text for forms, SMS and social posts.

1 min read

SUBTOTAL in Excel: Totals That Respect Filters

Use SUBTOTAL to sum, count or average only visible rows after filtering, the difference between 9 and 109, and why it ignores other subtotals.

How-To

2 min read

Percentage Formula in Excel: 5 Calculations You'll Use

How to calculate percentages in Excel: percent of total, percentage of a number, adding or taking off a percentage, and formatting. With the $ fill-down fix.

1 min read

Percent Change in Excel: Increase, Decrease and Growth

Calculate percent change in Excel with (new-old)/old, month-over-month growth with fill down, handling zero starting values, and compound annual growth (CAGR).

1 min read

Weighted Average in Excel: SUMPRODUCT Made Simple

Calculate a weighted average in Excel with SUMPRODUCT and SUM: grades with credits, average price by quantity, and why plain AVERAGE misleads.

1 min read

Running Total in Excel: Cumulative Sum With One Formula

Create a running total in Excel with =SUM($B$2:B2) filled down, why the half-locked range works, and alternatives for tables, filters and Microsoft 365.

2 min read

Fill Down in Excel: Copy a Formula to Every Row

Four ways to fill a formula down in Excel: drag the fill handle, double-click it, Ctrl+D and tables. Plus how references change and how to check the copies.

1 min read

How to Count Cells With Text in Excel

Count cells with any text, exact text, or text containing a word in Excel using COUNTIF and wildcards, without counting blanks or numbers.

1 min read

Remove Duplicates in Excel: 4 Methods (With Formulas)

Remove duplicates in Excel with the built-in button, UNIQUE, or COUNTIF flags, and highlight duplicates before deleting. Plus the hidden-space problem.

1 min read

Compare Two Columns in Excel: Find Matches and Differences

Compare two columns in Excel row by row or as lists: find matches, missing items and differences with =, IF, COUNTIF, XLOOKUP and conditional formatting.

1 min read

Combine First and Last Name in Excel (3 Easy Ways)

Combine first and last names in Excel with &, CONCAT or TEXTJOIN, add initials, format as Last, First, and fix capitals and extra spaces.

1 min read

Split Text in Excel: Into Columns by Space, Comma or Dash

Split text in Excel with Text to Columns, Flash Fill, TEXTSPLIT, or LEFT/MID/FIND formulas. Examples for full names, addresses and codes with separators.

1 min read

How to Calculate Age in Excel From a Date of Birth

Calculate age in Excel from a birth date with DATEDIF or YEARFRAC, show years and months, age on a specific date, and avoid the divide-by-365 mistake.

1 min read

Profit Margin in Excel: Formulas for Profit, Margin and Markup

Calculate profit, profit margin and markup in Excel with clear formulas, the bracket mistake to avoid, and how to work out a selling price from a target margin.

2 min read

How to Make a Budget Spreadsheet in Excel (Step by Step)

Build a monthly budget in Excel: transactions table, SUMIF by category, budget vs actual, percentages and a running balance. Formulas included.

1 min read

Grade Calculator in Excel: Averages, Weights and Letter Grades

Build a grade calculator in Excel: weighted scores with SUMPRODUCT, letter grades with IFS or a lookup table, class statistics, and pass/fail counts.

1 min read

Calculate Time Difference in Excel (Hours, Minutes, Overnight)

Calculate time differences in Excel: hours worked, decimal hours for pay, overnight shifts, totals over 24 hours, and the [h]:mm format that fixes odd results.

2 min read

F4 in Excel: The Shortcut That Adds $ Signs for You

Press F4 while editing a formula to cycle $B$2, B$2, $B2 and B2. Mac and laptop equivalents, the repeat-action trick, and why F4 may not work.

2 min read

Excel Formula Shortcuts: 15 Keys That Save Hours

The Excel keyboard shortcuts for working with formulas: AutoSum, fill down, F4 locks, show formulas, evaluate, insert function and more, for Windows and Mac.

2 min read

Conditional Formatting With Formulas in Excel

Use formulas in conditional formatting to highlight whole rows, overdue dates, duplicates, top values and targets, with the $ reference rule that makes it work.

Job Test Prep

3 min read

Excel Test for a Job Interview: What's Tested and How to Prepare

What pre-employment Excel tests cover at basic, intermediate and advanced levels, sample tasks, a 2-week prep plan and tips for test day.

3 min read

Excel Interview Questions and Answers (Beginner to Advanced)

Common Excel interview questions with short model answers: references, IF, VLOOKUP vs XLOOKUP, pivot tables, errors and data cleaning.

2 min read

Basic Excel Test Questions With Answers (Practice Test)

A free practice Excel test with 15 questions like those in pre-employment assessments: formulas, $ references, IF, COUNTIF, SUMIFS and VLOOKUP, with answers.

2 min read

Excel Assessment Practice: How to Train for Timed Tests

How to practise for a timed Excel assessment: question types, time management, checking answers fast, and what to do when you're stuck.

2 min read

Excel Skills for Your Resume: What to List and How to Prove It

Which Excel skills to put on your resume, how to describe your level honestly, and examples of bullet points that show real results instead of buzzwords.

2 min read

Advanced Excel Skills: What They Are and Which to Learn First

A clear list of advanced Excel skills employers mean: lookups, pivot tables, dynamic arrays, data cleaning, Power Query and more, with what to learn first.

1 min read

Excel Skills for Data Entry Jobs: What You Need to Know

Excel skills data entry jobs test: fast, accurate entry, formatting, validation, sorting, filtering, and COUNTIF and lookups to check your work.

1 min read

Excel for Accountants: The Formulas and Skills That Matter

Essential Excel skills for accountants and bookkeepers: SUMIFS for ledgers, lookups for reconciliations, ROUND, dates, absolute references and error checks.

1 min read

Excel for Administrative Assistants: Skills Employers Test

Excel skills admin roles ask for: lists and trackers, sorting and filtering, totals and date formulas, mail-merge-ready data and basic lookups.

1 min read

Excel for Data Analysts: Skills, Formulas and What to Learn Next

Excel skills data analysts need: cleaning, lookups, SUMIFS, pivot tables, dynamic arrays and charts, and how Excel fits with SQL and BI tools.

2 min read

Excel for Financial Analysts: Core Skills and Formulas

Excel skills financial analyst roles test: modelling structure, absolute references, SUMIFS, lookups, growth and CAGR, NPV and IRR, scenarios and error checks.

1 min read

Excel for Sales: Track Pipeline, Targets and Commissions

Practical Excel for sales teams: track pipeline and targets, calculate commissions with tiers, summarise by rep and month, and rank performance.

1 min read

Excel for Project Managers: Trackers, Timelines and Budgets

Excel for project managers: task trackers, working-day timelines, a simple Gantt chart with conditional formatting, budget vs actual and RAG flags.

1 min read

Excel for HR: Headcount, Tenure, Leave and Salary Formulas

Excel formulas for HR teams: headcount by department, tenure with DATEDIF, leave balances, salary bands with lookups, turnover rate and clean employee lists.

Google Sheets

2 min read