This is a collection of Excel tricks that I collect in case of future use. Here are a couple of informational YouTube Channels that introduce excel tricks: PAPAYA(In Chinese), MyOnlineTrainingHub

updated 231129

Functions

Count / Sum / Lookup

XLOOKUP for looking up value and return a corresponding cell, both EXACT and FUZZY match
similar function: LOOKUP / VLOOKUP / HLOOKUP

OFFSET(A1, 3, -5) Start from Cell A1. Move right 3 and move down -5 (up 5)

SUMIF(A:A, “>”&C3, B:B) B:B is the array to be added, C3 is the cell to refer to
similar functions: SUBTOTAL / SUMIF(S) / SUMPRODUCT / COUNTIF(S)
SUM(COUNTIF(range,”>”&{lower_limit,upper_limit})\{1,-1})*

D-Series DSUM / DCOUNT(A) / DAVERAGE / DGET(alternatives of LOOKUP series) / DPRODUCT / DMAX / DMIN / DVAR(P) / DSTDEV(P) D means database, dealing with an existing database

AGGREGATE can do sum/count/average when encountering ERROR, NA, hidden rows

Some simple functions also listed here in case forgottenAND() and OR(), UNIQUE()

CONVERT(number, “unit”, “unit”) convert between common units

Manipulation

LET(Var1,Val1, Expression) to retrieve the value of variable can call for calculation immediately

LAMBDA(Var1, Expression)(Val1) should be defined in Name Manager to be called like a function

Differences between LET and LAMBDA: LET is more of a customized expression with local variable defined at the beginning, while LAMBDA is more like a function in programming language - function is set first then values of variables are passed to the function. This characteristic enable the Recursion for LAMBDA. e.g.

7 LAMBDA helper function: MAP, SCAN, BYCOL, BYROW, REDUCE, ISOMITTED, MAKEARRAY

INDIRECT(A1) Function as a pointer. An address is passed to INDIRECT function.

INDEX(range, row_num, column_num) Return the value of a specified cell in a range
MATCH(lookup_value, lookup_array)
(somehow INVERSE of LOOKUP functions)

FILTER(array, include)

& align and combine two cells as text

# for dynamic array

GETPIVOTDATA a lighter version than creating a real pivot table

Finance / Regression / Prediction

PMT(rate, N periods, PV) Compounded Interests

FORCAST(x, known_y, known_x) Linear or Exponential Forecasting or Data | Forecast Sheet for predictions including seasonality

For dates operation: EDATES, NETWORKDAYS.INTL, DAY, TODAY

Operations / Skills

Shortcuts/Hotkeys “Alt” and follow what’s shown on top

Fill Series e.g. Quickly generating 1~1000 in a column, functions doing similar things is SEQUENCE

Name Manager for reusing a cell value or LAMBDA function

Slicers - 交叉分析筛选器: Create Table | Table Design Tab | Insert Slicer can be used for building up dashboard

For large amount of data, go to Power Query first, or you need, Python Numpy/Pandas or MATLAB.

Data | Text-to-Columns

Recording Macros is a good approach for repeated operations, which can be later turned into VBA scripts (redundance exists).

Q&A

Q:
How to COUNTIF multiple sections without helper column/row?

A:
If odd/even columns:
SUM((range)*( MOD(COLUMN(range),2)=1 )
Similarly, SUMPRODUCT can be used for summing odd/even rows:
SUMPRODUCT((range)*(MOD(ROW(range),2)=1)
If multi-irregular region:
SUM(COUNTIF(INDIRECT({“region1”,”region2”,”region3”}),criteria))
See this link for another approach with VSTACK function.

Better Layout

Some of personal experience for better layout in excel:

Hide borders
Color selection (60%-30%-10% principles)
Switching color every line for better reading
Highlight important cells

Extensions

excel: funfun(javascript), Data Chart
ppt: islide

More on Excel learning

When I got into college, I considered Python tops all productivity tools including Excel. However, after some internships experiences and coding/excel courses, Excel still have its merits because of the following reasons:

Accessibility: it is on literally every single computer, no need to configure the environment, especially when your company restrict external installation. Also, it allows connection with PPT so that allowing auto-refresh plot and tables in slides!
Direct presentation: all data are presented directly and it’s easy to locate the data needed. Conversion to plots is also quite handy. Popular videos on making Dashboard with Excel Pivot table are also an impressive example. See the brilliant example of Mccabe-Thiele Method in Distillation.
Easy debugging: Since all data are presented, compared with scripts. And this advantage provides better transferability, especially when you have to send a table to another colleague not that familiar with programming. Believe me, teaching excel is much easier than teaching coding.
Capability to handle big data: Though the limit of Excel is 1048567 rows, which is quite sufficient for most data handling, Power Query and VBA make it possible to handle larger amount.

I may also think of some drawbacks of Excel that one may consider employ coding to solve:

Higher dimensional dataset (Pivot table might help)
Infinite recursion (VBA can do it, LAMBDA might help, but most of the time, not an Excel thing)
Machine learning related packages

Learning programming language is definitely helpful, while Excel is by all means useful for analysts’ career.

If asked about how to be better at Excel? By a glimpse making fancy dashboards, complicated functions, automated macro/VBA, they look terrifying (Yes!), but the logics behind is actually not that complicated. The MOST important thing is: ask the correct question to google/chatGPT and you will get to know a bunch of functions and hotkeys that speed of your job most. Read example codes / watch videos to understand the syntax. After a couple of applications, you will get familiar to them and become an expert (I am not one yet, but I believe you can make it!)