Home

Welcome to Encyclopedia Excel. Tutorials, Tricks, Macros and More

Explore some Excel's built-in functions, follow guided learning courses, or take a dive into macros and VBA, all with concise, easy to follow, examples and tutorials.

The primary focus is currently on Excel and Excel related topics, with the occasional business intelligence, data analytics, and statistics related tutorials.

Tutorials

From simple formulaic solutions, everyday problems, chart design, all the way to building out full dashboards, this section will have you covered.

Click here for a list of all available tutorials

Update Pivot Caches on Copied Workbooks with VBA

When a connection to a Pivot Cache won't break on a copied workbook, this macro will help them update correctly.

Sum Across Multiple Dynamic Arrays by Row

How to sum across multiple dynamic arrays in a dynamic, flexible formula using the BYROW and LAMBDA Functions.

Extract the Regular or Fiscal Quarter From Any Date

An easy way to pull the correct quarter from any date on a regular or fiscal calendar.

Functions

Add-in and Automation - Aids in the dynamic integration of external data sources

Cube - Retrieve values from data models to build customized reports without relying on pivot tables

Database - Used to manipulate and analyze data stores in lists or databases

Date and Time - Assists with manipulation and calculation of various dates and times

Engineering - Common engineering functions, and ability to convert between different bases and complex numbers

Financial - Basic financial calculations such as future value, discount rate, depreciation, and yield.

Information - Gives meta info from workbooks and compares logical values

Logical - Allows the use of logical operators like "If/And/Or/Xor" across a range of different criteria.

Lookup - Filters and returns specified data

Math and trigonometry - Commonly used mathematical functions and operators

Statistical - Provides statistical analysis tools to better understand correlations and deviations

Text - Gives control over any text related field, searching, converting, formatting, and editing

Web - Allows the collection of data from the web using xml and xpath

Functions

Explore Excel

(Click the ribbon to navigate)

Take a deep-dive into the excel toolbar and ribbon menus, and discover all of the obscure functions and abilities you never knew (or wanted to know) excel had.