Overview.
Microsoft Excel is a spreadsheet program used to organize, analyze, calculate, and visualize data in rows and columns. Its core structure uses cells, where data is entered, allowing for complex calculations and data insights, making it essential for professionals in many fields.
Excel interface.
Excel's user interface is designed for efficiency, with a grid-based workspace where data is organized into rows and columns.
A workbook is the file containing multiple tabs at the bottom
A worksheet is a grid of cells identified by column letters A,B,C and row numbers 1,2,3.forming addresses like A2,b3.
Ribbon is the top toolbar with tabs like Home, Insert, Formulas, Data, and Review.
The Formula bar displays the content of the selected cell.
The Name box shows the active cell's address or named ranges.
The Status bar at the bottom, it provides quick stats like sum or average of selected cells.
Removing duplicates.
In Excel, you can remove duplicates by selecting your data, going to the Data tab, clicking Remove Duplicates, choosing the columns to check, and confirming to keep only unique records
Conditional formatting.
Conditional formatting in Excel lets you automatically change the appearance of cells (colour, icons, data bars) based on rules, making patterns and issues easy to spot. You apply it by selecting your data, going to Home then conditional Formatting, choosing a rule such as highlighting values greater than a target, and setting the desired format.
Freeze panes.
Freeze Panes in Excel keeps specific rows or columns visible while you scroll through your worksheet, making large datasets easier to read. You use it by selecting a cell below the row and to the right of the column you want to keep visible.
Data filter
A data filter in Excel lets you display only the rows that meet specific criteria, making it easier to analyze large datasets. You apply it by selecting your data and clicking Data then Filter, then using the dropdown arrows in the column headers to filter by values, text, numbers, dates, or conditions.
Data sorting.
Data sorting in Excel arranges data in a specific order—such as A to Z, Z to A, smallest to largest, or by date—to make information easier to analyze. You sort data by selecting a column, going to Data then Sort, choosing the sort order and if needed multiple levels for more complex sorting
Excel functions.
Microsoft Excel functions are predefined formulas that perform specific calculations or operations on data. They are essential for data analytics, enabling users to manipulate, analyze, and visualize datasets efficiently without writing custom code from scratch
Types of excel functions.
Aggregate functions
Aggregate functions summarize multiple rows of data into a single meaningful value.
sum =SUM(B2:B100)
average =AVERAGE(B2:B100)
maximum =MAX(B2:B100)
minimum =MIN(B2:B100)
count =COUNT(B2:B100)
Conditional aggregation
Conditional aggregation is the process of summarizing data only when specific conditions are met.
SUMIF() aggregates values based on a single condition.
SUMIFS() aggregates values based on multiple conditions.
COUNTIF Used to count records that meet one or more conditions.
COUNTIFS Used to count records that meet multiple conditions.
Logical functions
logical functions automate decision making.
IF
If function performs a logical test and returns value if the test is true.
=IF(J2>6, "high performance", "low performance")
Nested IF
A nested if is simply an if formula inside another if formula. It lets you test multiple conditions.
=IF(R2>30,"Highly Experienced",IF(R2>20,"Moderately Experienced",IF(R2>10,"Low Experience","Very Low Experience")))
And
And function returns value when two conditions have to be met.
=IF(AND(R2>30,W2>10),"assign bonus", "do not assign")
Or
Or function returns value when either of the conditions have been met
=IF(AND(G2>1/1/2006, L2>6), "promoted", "not promoted")
Lookup functions.
VLOOKUP (vertical lookup) function is used to look up a value in the first column of a table and return a related value from another column.
=VLOOKUP(10871, A2:AA877, 5, FALSE) 5 is column index number.
HLOOKUP (Horizontal Lookup) is used when your lookup values are in the top row of a table and you want to return a value from a row below.
=HLOOKUP(10011, A1:AGS21, 4, FALSE)
10011 is data in a cell
INDEX
Index function returns the value from a actual position in a range.
=INDEX(G2:G877, MATCH(10871, F2:F877, 0))
MATCH
Match function Finds the position of a value in a range
Text functions.
Trim removes extra spaces. =TRIM(A2)
Len returns length of a string.=LEN(A2)
Left extracts the leftmost characters of a string. =LEFT(A2,4)returns first 4 characters.
Right function extracts the rightmost characters of a string. =RIGHT(A2,2) returns last 2 characters.
Mid extracts characters from the middle of a string.
=MID(b2,2,3)
Concatenation used to combine two texts.
=CONCAT(C2, " ", C3
Date and Time functions.
Now function returns the current date and time and updates automatically.
=NOW()
today function returns the current date
=today()
end of month returns end of 3 months =eomonth(f2,3) i.e. 22/1/2026 returns 30/4/2026
network function returns difference between two days excluding weekends.=networkdays(k2,j2)
date difference returns difference between two dates. =datedif(k2,j2, "M")
edate returns today's date added 3 months =edate(f2,3) ie 22/1/2026 returns 22/4/2026
Pivot tables, Pivot charts & Dashboards
Pivot table.
A Pivot Table is an Excel tool used to summarize, analyze, and explore data quickly without writing formulas.
To insert a pivot table on excel click on the data you want to create a pivot table then click on insert on the toolbar, then click on pivot table.
From the pivot table fields on the right, drag fields to the desired area i.e. filter, rows, columns, and values.
Slicers.
A Slicer is a visual filter that lets you interactively filter data in Pivot Tables and using clickable buttons.
Pivort charts.
Pivot Charts are charts that are directly linked to Pivot Tables. When the Pivot Table changes the chart updates automatically.
From the pivot table analyze menu click on the pivot chart and select the desired chart.
Dashboards.
A Dashboard is a single screen report that shows key metrics and insights using charts, PivotTables, and visuals so decision makers can understand performance at a glance.
To create a dashboard create a new worksheet, rename it to Dashboard. open the worksheet and select a wide area or ctrl+A then fill with your desired background colour. Copy your pivot charts and slicers from the separate worksheets and paste them on your dashboard worksheet. Add shapes to add text e.g. "dashboard title" then resize them to the desired sizes.
Top comments (0)