Basic Fundamental of Excel
I am doing this project as my mini-project for my Guided Learning
Why Excel?
-
Identify trends
-
Organize and sort data into meaningful categories
-
There is a total of 750 million users worldwide
How did I complete my Excel Guidebook
1. Using Google
2. Watching videos on YouTube
3. Clarifying with my lecturer when in doubt
Topics that I did for my project:
-
Chapter 3: Cell references, Mathematical and statistical functions
-
Chapter 6: Text functions
-
Chapter 9: Data modeling using PowerPoint Pivot
-
Chapter 12: Optimisation
Chapter 3: Cell References, Mathematical and Statistical Functions
Cell References (2 types)
-
Absolute:
-
Remain constant, no matter where they are copied.
-
Example: $E$4. It is to tell Excel to keep the cell reference E4 constant in the formula as you copy it to the destination cells.
-
Shortcut: F4 function key
-
-
Relative:
-
Change when a formula is copied to another cell
-
Tells excel to adjust the cell reference as it copies it to the destination cells
-
Example: A formula using relative cell reference E4 tells Excel to adjust the cell reference as it copies it to the destination cells.
-
-
A formula using mixed cell reference $E4 or E$4 will have only one-dollar sign before either the column or the row
Using Mathematical Functions
-
Example: SUM, SUMPRODUCT, and SUMIF to perform simple calculations.
Using Statistical Functions
-
Example: AVERAGE, AVERAGEIF, COUNT, COUNTIF, RANK, MAX, MIN, MEDIAN, MODE, STDEV, VAR, COVARIANCE to perform data analysis.
Cell References
Using Mathematical Functions
Using Statistical Functions
Chapter 6: Text Function
CONCATENATE Function and the ampersand (&)
-
Allows you to combine text, cell values, or formula outputs into a single text string in the following format.
-
Syntax
-
=CONCATENATE (Text1, Text2…)
-
A simpler way to get the same result: =A2 & "/ " & B
-
TRIM, UPPER/LOWER/PROPER function
-
Trim
-
Remove extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text
-
Useful when cleaning text that hascome from other application
-
Syntax: TRIM(text)
-
-
Upper
-
Converts all letters in a text string to uppercase
-
Syntax: UPPER(text)
-
-
Lower
-
Converts all letters in a text string to lowercase
-
Syntax: LOWER(text)
-
-
Proper
-
Capitalize the first letter in a string and it converts all other letters to lowercase letters
-
Syntax: PROPER(text)
-
LEFT/MID/RIGHT/LEN Function
-
Left
-
Returns a specified number of characters from the start of a supplied text string
-
Syntax: = LEFT (text, [num_chars])
-
-
Mid
-
Extracts text from inside a string
-
Syntax: = MID (text, start_num, num_chars)
-
-
Right
-
Extracts text from the right of a string
-
Syntax: = RIGHT (text, [num_chars])
-
-
Len
-
Calculates the length of the text. Useful when you want to count how many characters there are in some text.
-
Syntax: LEN(text)
-
Using Concatenate function
TRIM, UPPER/LOWER/PROPER function
Using LEFT/MID/RIGHT/LEN Function
Chapter 9: Data Modelling using PowerPoint Pivot
Power Pivot Add-in for Excel
-
To turn on Power Pivot COM add-ins
-
Click the File tab, click Options. And then click the Add-Ins category.
-
In the Manage box, click COM Add-ins, and then click Go.
-
The COM Add-Ins dialog box appears
-
Check the Microsoft Office Power Pivot for Excel option and click OK.
-
After the Power Pivot add-in is enabled, you will see a new Power Pivot tab on the ribbon.
-
Create relationships between tables
-
Create a table by clicking on Insert > Table
-
Key in the necessary information and click anywhere in the table and select Power Pivot and click on Add to Data Model.
-
At this point, the Power Pivot window opens by default in a data view, showing the contents of the table and you can see the tables is added and displayed in the Grid.
(Repeat the above steps to add the remaining tables into the data model.) -
Link the different table and the rest of the remaining tables into the data model.
Using Power Pivot Add-in for Excel
Chapter 12: Optimisation
Optimize solutions in business models
-
Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for the business models.
-
To activate:
-
Select File tab > Options > Add-ins.
-
Click Go… button with Excel Add-ins.
-
Check Solver Add-in and click OK button
-
Using Excel Solver
