Master Microsoft Excel

Your ultimate, free resource for Excel functions, formulas, PivotTables, charts, and VBA automation. Unlock the full potential of your data.

Core Excel Functions

Functions are predefined formulas that perform calculations using specific values, called arguments, in a particular order. Here are some of the most fundamental ones.

SUM

Adds all the numbers in a range of cells.

=SUM(A1:A10)

AVERAGE

Returns the average (arithmetic mean) of its arguments.

=AVERAGE(B1:B20)

COUNT

Counts the number of cells that contain numbers.

=COUNT(C1:C100)

MAX & MIN

Finds the largest (MAX) or smallest (MIN) value in a set of values.

=MAX(D1:D50)
=MIN(D1:D50)

Text Functions

Manipulate and format text strings with these powerful functions.

CONCAT & TEXTJOIN

Combines text from multiple cells. TEXTJOIN is more flexible as it allows a delimiter.

=CONCAT(A1, " ", B1)
=TEXTJOIN(" ", TRUE, A1:B1)

LEFT, RIGHT, MID

Extracts a specific number of characters from the start, end, or middle of a text string.

=LEFT(A1, 5)
=RIGHT(A1, 3)
=MID(A1, 7, 10)

LEN & TRIM

LEN returns the length of a text string. TRIM removes extra spaces from text.

=LEN(A1)
=TRIM(" extra spaces ")

UPPER, LOWER, PROPER

Changes the case of text to all uppercase, all lowercase, or proper case (first letter capitalized).

=UPPER("text")
=LOWER("TEXT")
=PROPER("text")

Logical Functions (IF, AND, OR)

Logical functions are used to introduce decision-making into your worksheets. They test whether a condition is true or false and return a value accordingly.

IF Function

Checks if a condition is met, and returns one value if TRUE, and another value if FALSE.

Example: If the value in A1 is greater than 50, return "Pass", otherwise return "Fail".

=IF(A1>50, "Pass", "Fail")

AND, OR, NOT

Used with IF to test multiple conditions. AND requires all conditions to be true. OR requires at least one to be true. NOT reverses the logic.

=IF(AND(A1>50, B1="Complete"), "Proceed", "Wait")

IFERROR

Returns a custom value if a formula evaluates to an error; otherwise, returns the result of the formula.

=IFERROR(A1/B1, "Cannot divide by zero")

Lookup & Reference Functions

These are some of the most powerful functions in Excel, allowing you to find and retrieve data from a table.

VLOOKUP & HLOOKUP

Looks for a value in the leftmost column (VLOOKUP) or top row (HLOOKUP) of a table, and then returns a value from the same row/column.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Note: VLOOKUP has limitations. For more flexible lookups, consider using INDEX and MATCH or XLOOKUP.

INDEX & MATCH

Used together, INDEX and MATCH provide a more powerful and flexible way to perform lookups. MATCH finds the position of a lookup value, and INDEX retrieves the value at that position.

=INDEX(C1:C100, MATCH("John Doe", A1:A100, 0))

XLOOKUP

The modern successor to VLOOKUP. It's more intuitive, flexible, and robust, able to look left and right, and has a built-in "if not found" argument. (Available in newer Excel versions).

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

Date & Time Functions

Perform calculations involving dates and times.

TODAY & NOW

Returns the current date (TODAY) or the current date and time (NOW). These are volatile functions and recalculate whenever the worksheet changes.

=TODAY()
=NOW()

DATE, YEAR, MONTH, DAY

Construct a date from individual components, or extract a specific component from a date.

=DATE(2024, 10, 26)
=YEAR(A1)
=MONTH(A1)
=DAY(A1)

EOMONTH & EDATE

Calculate the last day of a month (EOMONTH) or a date a specified number of months in the future/past (EDATE).

=EOMONTH(A1, 3) 'End of month 3 months from date in A1

Math & Trig Functions

Functions for a wide range of mathematical calculations.

SUMIF & SUMIFS

Sum values in a range that meet one (SUMIF) or multiple (SUMIFS) criteria.

=SUMIF(A1:A10, ">50", B1:B10)
=SUMIFS(C1:C10, A1:A10, "Sales", B1:B10, ">100")

ROUND, ROUNDUP, ROUNDDOWN

Rounds a number to a specified number of digits, always up, or always down.

=ROUND(123.456, 2) 'Result: 123.46

Statistical Functions

Perform statistical analysis on your data sets.

COUNTIF & COUNTIFS

Count cells in a range that meet one (COUNTIF) or multiple (COUNTIFS) criteria.

=COUNTIF(A1:A100, "Complete")

COUNTA

Counts the number of cells in a range that are not empty.

=COUNTA(A1:A100)

Building Powerful Formulas

A formula is an expression which calculates the value of a cell. You can combine functions, cell references, operators, and constants to create complex calculations.

For example, to calculate a 20% discount on a price in cell A2 and add a 7% sales tax, you could use:

=(A2 * 0.8) * 1.07

This formula first applies the discount (multiplying by 0.8) and then adds the tax (multiplying by 1.07).

Summarizing Data with PivotTables

A PivotTable is an interactive tool to quickly summarize large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data, and display the details for areas of interest.

  • Select your data or table.
  • Go to the `Insert` tab and click `PivotTable`.
  • Drag and drop fields into the Rows, Columns, Values, and Filters areas to build your report.
  • Instantly get sums, counts, averages, and other calculations without writing a single formula.

Data Visualization with Charts

Charts allow you to present data graphically, making it easier to see trends, patterns, and comparisons. Excel offers a wide variety of chart types.

Column Chart

Compare values across categories.

Line Chart

Show trends over time.

Pie Chart

Show proportions of a whole.

Introduction to VBA & Macros

Visual Basic for Applications (VBA) is the programming language of Excel. With VBA you can automate repetitive tasks, create custom functions, and build complex applications within Excel.

The easiest way to start is by using the Macro Recorder. It records your actions and converts them into VBA code, which you can then edit and customize.

Sub MyFirstMacro()
    ' This macro selects a cell and enters text.
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Hello, ExcelWiki!"
End Sub

Explore the Future: AI-Powered Spreadsheets

While traditional spreadsheets are powerful, the next generation of data tools leverages Artificial Intelligence to unlock even deeper insights. For those looking to explore a modern, web-based spreadsheet experience, SumBuddy offers a compelling alternative.

Meet SumBuddy: Your AI Spreadsheet Assistant

SumBuddy is an online spreadsheet that integrates cutting-edge AI to help you analyze data, generate formulas, and create visualizations with simple, natural language commands. It's perfect for students, professionals, and anyone curious about the future of data interaction.

Try the SumBuddy AI Spreadsheet

Pro Tips & Tricks

Boost your productivity with these powerful shortcuts and features.

  • Flash Fill: Automatically fill data when it senses a pattern. Start typing in a column adjacent to your data, and Excel will do the rest (Shortcut: `Ctrl + E`).
  • Conditional Formatting: Highlight interesting cells or ranges of cells, and visualize data by using data bars, color scales, and icon sets.
  • Keyboard Shortcuts: Use `Ctrl + Arrow Keys` to jump to the edge of your data, `Ctrl + Shift + L` to toggle filters, and `F4` to repeat your last action.