Data analysis Course

aua teChnologies

Date: 12th January, 2025

Subject: Microsoft Excel

Topic: Detailed description of some essential commands organized by the tabs in

Microsoft excel and some essential excel formulas with their syntax and a brief
explanation of their use:

Facilitator: Nazeer Aminu Suleiman

1. Home Tab
The Home Tab focuses on basic formatting, editing, and data manipulation
commands.

a. Clipboard Group

 Cut, Copy, Paste (Ctrl + X, Ctrl + C, Ctrl + V): Used to move or duplicate data.
 Paste Special: Provides advanced pasting options (e.g., values only Alt + H+V+V, formulas, or
formatting).

b. Font Group

 Bold, Italics, Underline (Ctrl + B, Ctrl + I, Ctrl + U): Format text styles.
 Font Color & Fill Color: Change text color or highlight cells.

c. Alignment Group

 Merge & Center: Combine multiple cells into one and center the content.
 Wrap Text: Adjust text to fit within the cell.
 Text Alignment: Align content vertically or horizontally within the cell.

d. Number Group

 Number Formatting: Change data types (e.g., General, Currency, Percentage, Date).
 Increase/Decrease Decimal: Adjust the number of displayed decimal places.

e. Editing Group
  AutoSum (Alt + =) : Quickly calculates the sum of selected cells.
 Find & Replace (Ctrl + F, Ctrl + H) : Locate specific text or values in your workbook.

2. Insert Tab
The Insert Tab is used to add visual and functional elements.

a. Tables Group

 Table: Converts data into a structured table with sorting and filtering. Shortcut: Ctrl + T.
 PivotTable: Summarizes data dynamically.

b. Charts Group

 Bar, Line, Pie Charts: Visualize trends, proportions, and comparisons.

 Sparklines: Mini charts within cells to show trends.

c. Illustrations Group

 Shapes, Icons, Pictures: Add visual elements to your sheet.

d. Links

 Hyperlink (Ctrl + K): Link text or objects to web pages, email, or other cells.

3. PAGE LAYOUT TAB

The Page Layout Tab focuses on preparing your workbook for printing and adjusting
the layout.

a. Page Setup Group

 Margins: Adjust the space between the content and the page edges.
 Orientation: Choose Portrait or Landscape mode for printing.
 Size: Set the paper size (e.g., A4, Letter).

b. Scale to Fit

 Adjust the scaling of the worksheet to fit on one page when printing.

c. Sheet Options

 Gridlines: Show or hide gridlines in your worksheet.

 Headings: Toggle row and column headers on or off.
 4. FORMULAS TAB
The Formulas Tab houses Excel’s calculation and logical tools.

a. Function Library

 AutoSum: Quick access to SUM, AVERAGE, COUNT, MIN, and MAX.

 Logical Functions: Includes IF, AND, OR.
 Lookup & Reference: Use VLOOKUP, HLOOKUP, INDEX, and MATCH.

b. Formula Auditing

in Excel helps you understand the relationships between formulas and cells in your
spreadsheet, ensuring accuracy and troubleshooting errors effectively. Here's a
breakdown of the features you mentioned:

 Trace Precedents/Dependents: Show which cells influence or are influenced by a specific

formula.
 Evaluate Formula: Step through a formula to understand its calculations.

C. Calculation Options in Excel

Excel provides two primary modes for recalculating formulas: Automatic and
Manual. These options determine when and how Excel updates formulas in your
workbook.

1. Automatic Calculation Mode

What it does:
Excel recalculates formulas automatically whenever you make changes to the data or
structure of the workbook.
This is the default mode in Excel.

When to use:

o For most tasks where real-time updates are important.

o When working with smaller datasets or workbooks with simple formulas, where
recalculation won't slow you down.

Example:
If C1 has the formula =A1 + B1, and you update A1 to a new value, C1 will instantly
reflect the updated result.

2. Manual Calculation Mode

What it does:
Excel recalculates formulas only when you manually trigger it. This is useful when
you’re working with complex spreadsheets that involve thousands of calculations
and recalculating after every change would slow down your workflow.

How to trigger manual recalculation:

o Press F9 to recalculate all formulas in the workbook.

o Press Shift + F9 to recalculate only the active worksheet.

When to use:

o When working with large or complex workbooks to prevent lag.

o If you want to control when calculations occur, especially during data entry or
testing.

Example:
In manual mode, if C1 depends on A1 and B1, updating A1 will not immediately update
C1. You need to press F9 to see the updated result.

How to Switch Between Modes

1. Go to the Formulas tab in the Ribbon.

2. In the Calculation group, click Calculation Options.
3. Choose one of the following:

o Automatic
o Automatic Except for Data Tables (similar to automatic but skips recalculating data
tables for efficiency)
o Manual

When to Switch Modes

 Use Automatic when accuracy and real-time updates are more important than speed.
 Use Manual for better performance when handling:
o Large datasets.
o Workbooks with complex formulas

5. DATA TAB
The Data Tab is for managing and analyzing datasets.

a. Get & Transform

 Import data from external sources (e.g., databases, CSV files).
What is a CSV file (short for Comma-Separated Values) is a simple text
file that stores data in a tabular format. Each line in the file represents a
row, and the values within that row are separated by commas (or
sometimes other delimiters like tabs or semicolons).

Key Characteristics of a CSV File

1. Plain Text: CSV files are simple text files, which makes them
lightweight and easy to share.
2. Tabular Data Format:
The data is organized into rows and columns. For example:
Name,Age,Country
John,30,USA
Mary,25,Canada
Ali,35,UAE

3. Delimiters: The default separator is a comma (,). Sometimes, other

delimiters like tabs (\t), semicolons (;), or pipes (|) are used, depending
on regional settings or specific software preferences.
4. No Formatting: CSV files don’t support advanced formatting (like bold
text, colors, or formulas). They only contain raw data.
5. File Extension: CSV files typically have the .csv extension.

Advantages of CSV Files

 Widely Compatible: Almost all spreadsheet programs (like Excel,

Google Sheets) and data analysis tools can open or export CSV files.
 Lightweight and Simple: Ideal for sharing and storing data without
large file sizes.
 Easily Readable: Both humans and machines can interpret the file
contents easily.

Disadvantages of CSV Files

 No Data Validation: CSV files don’t enforce data types or rules,

which may lead to inconsistencies.
 Limited Features:
No support for formulas, charts, or formatting
 Risk of Ambiguity:
If a value contains a comma (e.g., "New York, USA"), it can confuse
programs unless the value is enclosed in quotes ("New York, USA").
Common Uses

 Data Transfer:
CSV is a common format for transferring data between systems (e.g.,
exporting contacts, importing data into databases).
 Data Analysis:
Often used in data analysis tools (Python, R, Excel, etc.).

b. Data Tools

 Text to Columns: Split text into separate columns.

 Remove Duplicates: Eliminate duplicate rows.
 Data Validation: Create drop-down lists or restrict input.

c. Sort & Filter

 Sort: Arrange data by a specific order (e.g., ascending or descending).

 Filter: Display rows that meet specific criteria.

6. REVIEW TAB
The Review Tab focuses on proofing and protecting your workbook.

a. Proofing : Spelling (F7): Check for spelling errors.

b. Comments: New Comment: Add notes to cells.

c. Protect
 Protect Sheet: Lock certain cells or the entire sheet to prevent editing.
 Protect Workbook: Prevent structural changes to the workbook.
 7. VIEW TAB
The View Tab customizes how you view your workbook.

a. Workbook Views

 Normal View: Default view.

 Page Layout: Shows how the sheet will appear when printed.
 Page Break Preview: Adjust page breaks for printing.

b. Freeze Panes

 Lock rows or columns in place while scrolling. Example: Freeze the top row with Freeze Top
Row.
ESSENTIAL EXCEL FORMULAS WITH THEIR SYNTAX
AND A BRIEF EXPLANATION OF THEIR USE:
1. SUM

 Syntax: =SUM(number1, [number2], ...)

 Purpose: Adds up numbers, ranges, or a combination of both.
 Example: =SUM(A1:A10) adds all the values in cells A1 through A10.

2. SUMIF

 Purpose: Adds up cells that meet a specific condition.

 Syntax: =SUMIF(range, criteria, [sum_range])
 Example: =SUMIF(A1:A10, ">50", B1:B10)
This adds up the values in B1:B10 where the corresponding cells in A1:A10 are greater than
50.

3. AVERAGE

 Syntax: =AVERAGE(number1, [number2], ...)

 Purpose: Calculates the average (mean) of the numbers provided.
 Example: =AVERAGE(B1:B10) gives the average of values in cells B1 through B10.

4. IF

 Syntax: =IF(logical_test, value_if_true, value_if_false)

 Purpose: Performs a logical test and returns one value for TRUE and another for FALSE.
 Example: =IF(A1>100, "High", "Low") returns "High" if A1 is greater than 100; otherwise, it
returns "Low."
 Example: =IF(C1>50, "Pass", "Fail")
This checks if C1 is greater than 50; if true, it returns "Pass", else "Fail".

5. COUNTIF

 Purpose: Counts the number of cells that meet a specified condition.

 Syntax: =COUNTIF(range, criteria)
 Example: =COUNTIF(A1:A10, ">10")
This counts the number of cells in A1:A10 that are greater than 10.

6. INDEX

 Syntax: =INDEX(array, row_num, [column_num])

 Purpose: Returns the value of a cell within a specified range based on row and column
numbers.
  Example: =INDEX(A1:C10, 3, 2) returns the value in the 3rd row and 2nd column of the range
A1:C10.

7. MATCH

 Syntax: =MATCH(lookup_value, lookup_array, [match_type])

 Purpose: Returns the relative position of a value in a range.
 Example: =MATCH(50, B1:B10, 0) finds the position of the value 50 in the range B1:B10.

8. CONCATENATE (or CONCAT in newer versions)

 Syntax: =CONCATENATE(text1, [text2], ...)

OR =CONCAT(text1, [text2], ...)
 Purpose: Joins two or more text strings into one.
 Example: =CONCATENATE(A1, " ", B1) combines the values of A1 and B1 with a space in
between.

9. LEN

 Syntax: =LEN(text)
 Purpose: Returns the number of characters in a string (including spaces).
 Example: =LEN(A1) returns the length of the text in A1.

10. TRIM

 Syntax: =TRIM(text)
 Purpose: Removes all extra spaces from text, leaving only single spaces between words.
 Example: =TRIM(A1) cleans up unnecessary spaces in A1.

11. LEFT

 Syntax: =LEFT(text, [num_chars])

 Purpose: Extracts the specified number of characters from the start of a text string.
 Example: =LEFT(A1, 5) returns the first 5 characters from the value in A1.

12. RIGHT

 Syntax: =RIGHT(text, [num_chars])

 Purpose: Extracts the specified number of characters from the end of a text string.
 Example: =RIGHT(A1, 3) returns the last 3 characters from the value in A1.

13. MID

 Syntax: =MID(text, start_num, num_chars)

 Purpose: Extracts text from the middle of a string, given a starting position and length.
 Example: =MID(A1, 3, 4) extracts 4 characters starting from the 3rd character in A1.
14. ROUND

 Purpose: Rounds a number to a specified number of digits.

 Syntax: =ROUND(number, num_digits)
 Example: =ROUND(A1, 2)
This rounds the value in A1 to 2 decimal places.

15. NOW

 Syntax: =NOW()
 Purpose: Returns the current date and time.
 Example: =NOW() outputs the current date and time dynamically.

16. TODAY

 Syntax: =TODAY()
 Purpose: Returns the current date without the time.
 Example: =TODAY() outputs today's date.

17. COUNT

 Syntax: =COUNT(value1, [value2], ...)

 Purpose: Counts the number of numeric values in a range.
 Example: =COUNT(A1:A10) counts the numeric values in the range A1:A10.

18. IFERROR

 Syntax: =IFERROR(value, value_if_error)

 Purpose: Returns a custom value if a formula results in an error.
 Example: =IFERROR(A1/B1, "Error") displays "Error" if B1 is 0.

19. NOW

 Purpose: Returns the current date and time.

 Syntax: =NOW()
 Example: =NOW()
This will return the current date and time (e.g., 2025-01-12 10:00 AM).

20. TODAY

 Purpose: Returns the current date.

 Syntax: =TODAY()
 Example: =TODAY()
This returns the current date (e.g., 2025-01-12).

21. AND

 Purpose: Checks whether all conditions in a test are TRUE.

 Syntax: =AND(logical1, [logical2], ...)
 Example: =AND(A1>10, B1<20)
This returns TRUE if both conditions (A1>10 and B1<20) are met.
22. OR

 Purpose: Checks whether any condition in a test is TRUE.

 Syntax: =OR(logical1, [logical2], ...)
 Example: =OR(A1>10, B1<5)
This returns TRUE if either A1>10 or B1<5 is true.

23. UPPER

 Purpose: Converts all letters in a text string to uppercase (capital letters).

 Syntax: =UPPER(text)
 Example: If cell A1 contains the text "hello", =UPPER(A1) This will return "HELLO".

24. LOWER

 Purpose: Converts all letters in a text string to lowercase.

 Syntax: =LOWER(text)
 Example:
If cell A1 contains the text "HELLO", =LOWER(A1) This will return "hello".

25. PROPER

 Purpose: Capitalizes the first letter of each word in a text string and converts all other letters
to lowercase.
 Syntax: =PROPER(text)
 Example:
If cell A1 contains the text "hello world", =PROPER(A1) This will return "Hello World".