MS Excel


This is an attempt to understand data and analytics using MS Excel.

MS Excel V16.4

Name Range

Name ranges could be used as variables to put into formulas. Makes the workflow easy and convenient to search and manage.


COUNT -- Considers only numbers.
COUNTA -- Considers text

Average functions
AVERAGE -- Considers only numbers ; Omits text from counting.
AVERAGEA -- Considers text in the counting.

Decimal position +2 is hundredth decimal place. Similarly -3 is thousands place.
ROUNDUP and ROUNDDOWN are like Ceil() and Floor().
Subtotals :
Grouping and totalling data wrt changes in columns. Does data folding as well.

Date and Time functions

Text Functions

CHAR and CODE convert from and to ASCII

Statistical functions


Vlookup could be used along with Data -> Data Validation -> Allow "List" to create a dropdown for the possible values. Lookup value should be unique and should belong to the first column of the array.

Lookup vector should be sorted in ascending order and should have unique values

Pivot tables

Pivot tables are used to aggregate data and group them w.r.t criteria. Here's a reference video to understand the expected behaviour.


Slicers are visual filters in the form of interactive buttons. They are used to filter the data present in the pivot table.

Pivot table timelines

Pivot charts

Ordered pair functions

Correlation: CORREL


The solver add-in could be used to solve optimisation problems.

Last updated: 21 Jun 2021, 12 Sept 2020