Microsoft Excel is a spreadsheet software that was developed by Microsoft. It is the foundation on which analytical thinking is built. It is used for organizing, analyzing and visualizing data
Excel is just a big calculator but with some advantages like;
It can store data.
-It can perform multiple calculations at the same time.
-It can do operations on other forms of data such as text.
Excel is a powerful tool and you should let it work for you.
Vocabulary
You can think of this into two; the part of Excel that you work with such as cells, columns, and the tasks that you want to do such as cleaning, sorting and formatting.
Excel is made up of two sections; the upper part called the ribbon and the lower part called the sheet
This is the ribbon
This is the sheet or the worksheet.
Many worksheets make up a workbook
A worksheet is made up of cells, rows and a column.
A cell is denoted by it letter-number address.The letter is the column and the number is the row. A row moves from left to right while a column moves up to down.
This is cell E2
A range is a collection of cells. it is denoted using a range operator
This range is denoted by A2:C6
Formulas and Functions
Formulas are created by the user e.g., (10+10), (A6*B6)
Functions on the other hand are built in formulas. they are codes designed for calculations e.g. Sum(A2:B2), =Average(C2:C30). Don't worry. You'll get the hang of it as we go on.
Data Analysis Tools
This is where the magic with your hands begins. You have tools such as sort ad filter, formatting, data validation to analyze and interpret data.
Charts and Graphs
These are used to visually represent your data.
Lets now begin learning about some of what we have talked about above.
Formulas and Functions
Formulas
You make formulas using arithmetic operations, i.e. addition(+), subtraction(-),multiplication(*), division(/) exponent(^).
The values are typed directly and by using cells.
This shows multiplication calculation =(L2*M2)
For the others, you follow the same operation
Before we go to anything else, lets talk about parentheses. These are very important in Excel.
They dictate the order of a calculation. Having them or not having them is crucial.
No parentheses
=10+10*10. The result is 110 as the calculation is 10+100
With parentheses, the same result is 200 as Excel calculates it as 20*10
Data Analysis
There are three types of data namely text data, number data and date and time data
Different operations can be performed on these using different data analysis tools.
Lets start with sort and filter
Sort
3 types based on the data type:
-Text sorting(A-Z/Z-A)
-Number sorting(Largest-Smallest/Smallest-Largest)
-Date sorting(Oldest to Newest/Newest to Oldest)
Go to Home, Sort, choose expand the selection the click OK
The sorting operation is under the editing group.
Filter
This allows you to display only the rows and columns that meet a certain criteria and temporarily hide the rest.
it also has three types based on the type of data, i.e. text filter, number filter and date filter.
Go to Home ribbon. It is the "twin brother" to the Sort function.
Quick tip; you can click on your data and type Ctrl + Shift + L
Conditional Formatting
This is a function that is used to change the appearance of cells in a range based on your specified conditions, e.g. color scale formatting.
It is found in the Home ribbon.
You can also condition format cell rules. This options include greater than, less than, equal to, text that contains, between, duplicate/unique values
Here, we have given Excel specific conditions using the greater than option.
Excel Functions
They are divided into;
-Aggregate functions, eg. sum, average, min
-Logical functions, e.g. If, And, Or
-Text functions e.g. Uppercase, Lowercase, Trim, Right, Left
-Lookup functions e.g. vertical, horizontal, xlookup
-Date/Time functions
Text Functions
They are used to manipulate text data.
-Upper - Uppercases what you want
- Lower - Lowercases what is in a cell.
- Proper - Capitalizes only the fist letter -Trim - Used to remove extra unwanted space Length - Shows the number of characters in a cell including the spaces
- Left - Extracts the first letters, e.g.
The number 2 shows the number of characters you want to extract
- Right - Extracts the last letters. Prompts same as Left function
- Mid - Extracts characters in the middle Prompts same as Left function
- Concatenate - Used to combine two words e.g.
If you want them to separate:
Aggregate Functions
- Sum-
Very simple, right.
The other functions, i.e. Average, Maximum, Minimum, Count follow the same procedure.
SumIf, AverageIf,
They are used for conditional aggregation e.g. average age of people above 40 years
SumIf- Sum of people above 40 years old
AverageIf is also done the same way
Logical Functions
They are used to perform logical test and return one value for TRUE and another for FALSE.
E.g. column E has the salary of employees. The scenario is that employees above 80,000 are high while the others are low. How do you go about it?
=IF(E2>80000,"High","Low")
From here, you can now autofill.
And
Used when we want to meet two conditions such as salary and age.It is performed the same way as the IF function.
Or
We use it when we want to meet only one condition.
LookUp Functions
Just as the names suggests, they are used to "look up" for values in another column or row.
Three types namely Vertical(VLookup), Horizontal(HLookup) and XLookup.
Vertical Lookup
It searches for a value in the first column over a range and returns a value from another range.
E.g. we have Employee with ID 10009 and we want to know his department
=VLOOKUP(10009,A2:E15,4,FALSE)
10009 is his ID, A2:E15 is the range we want to work with, 4 is the column index where you want your answer from AND you write FALSE if you want the exact value.
If you want the approximate, write TRUE
HLookup
It searches for a value in the first row of a sheet and returns a value from another sheet.
The data is structured horizontally, that is, the rows have headers.
Its operation is the same as that for VLookup
XLookup
Also known as Index Matching.
Our reference is neither in the first column or first row. So what do we do?
Let's say the ID is in column F and we want t find his department in column D.
We use 0 as it gives us an exact value.
Pivot Charts and Pivot Tables
These are used to summarize our data.
They are very easy to work with.
How do you create them? Click anywhere on your sheet and go to Insert Ribbon and click Pivot Tables.
Always click on New Worksheet.
In the new worksheet, you now work your magic. Add the headers you want to the columns, rows and values table. That is it. you will have created your pivot table
You can now use this pivot table to create charts such as Column, Bar, Line and Pie charts
And that is it! A rundown on Excel. You may not get the hang of Excel right away. Don't worry. Every beginner was once there. The skills you learn here will carry over into your Data journey so get to learn it thoroughly.
Top comments (0)