Excel for Absolute Beginners: Essential Tips to Get You Started

by Sam McKay, CFA | Excel

Table of Contents

Excel Interface and Basic Functions

Objective

Practical guide for beginners to navigate Excel and use basic formulas and data analysis tools effectively.

Setup Instructions

Open Microsoft Excel.
Create a new workbook.

Excel Interface

Key Elements

Ribbon: Contains tabs like Home, Insert, Page Layout, Formulas, Data, Review, and View.
Formula Bar: Used to enter data or formulas in a selected cell.
Worksheet Cells: Grid of rows and columns where data is entered.

Basic Functions

1. Entering Data

- Click any cell (e.g., A1).
- Type "Hello, Excel".
- Press Enter.

2. Basic Formulas

Sum

Click cell A2.
Type: =SUM(1, 2, 3)
Press Enter. Result: 6

Average

Click cell A3.
Type: =AVERAGE(10, 20, 30)
Press Enter. Result: 20

3. AutoSum

- Type numbers in A1 to A3.
  Example: A1: 10, A2: 20, A3: 30
- Click cell A4.
- Go to Home > AutoSum (?).
- Press Enter. Result: `60`

4. Basic Data Analysis Tools

Sorting Data

- Select columns with data (e.g., Column A).
- Go to Data > Sort.
- Choose Ascending or Descending.

Filtering Data

- Select the data range.
- Go to Data > Filter.
- Click the dropdown arrow in the column header to filter data.

5. Basic Conditional Formatting

- Select the range to format (e.g., A1:A10).
- Go to Home > Conditional Formatting.
- Select Highlight Cells Rules > Greater Than.
- Enter a value (e.g., 10).
- Choose a format and press OK.

Key Shortcuts

Ctrl + C: Copy
Ctrl + V: Paste
Ctrl + Z: Undo
Ctrl + S: Save
Ctrl + Arrow Key: Navigate to the edge of data region

Conclusion

This guide should help you start using Excel effectively. Continue exploring more functionalities like pivot tables, VLOOKUP, and charts for advanced analysis.

Basic Excel Formulas Implementation

Sum

To add a range of numbers:

=SUM(A1:A10)

Average

To calculate the average of a range of numbers:

=AVERAGE(B1:B10)

Count

To count the number of cells in a range that contain numbers:

=COUNT(C1:C10)

Maximum

To find the maximum value in a range of numbers:

=MAX(D1:D10)

Minimum

To find the minimum value in a range of numbers:

=MIN(E1:E10)

If

To perform a logical test and return one value if true, another if false:

=IF(F1 > 10, "Yes", "No")

VLOOKUP

To search for a value in the first column of a range, and return a value in the same row from another column:

=VLOOKUP(G1, H1:J10, 2, FALSE)

Concatenate

To join together two or more text strings:

=CONCATENATE(I1, " ", J1)

Date

To get the current date:

=TODAY()

Sumif

To sum a range based on a condition:

=SUMIF(K1:K10, ">10", L1:L10)

Countif

To count the number of cells in a range that meet a condition:

=COUNTIF(M1:M10, ">=20")

Text

To convert a number into text with a specified format:

=TEXT(N1, "0.00")

And

To check if all conditions are true:

=AND(O1>5, O2<10)

Or

To check if any condition is true:

=OR(P1="Yes", P2="No")

Usage Notes

Replace A1:A10, B1:B10, etc., with your specific cell ranges.
Replace logical conditions and text within formulas to fit your requirements.
Adjust cell references and ranges as applicable to your particular Excel sheet structure.

Working with Text Functions in Excel

1. CONCATENATE Function

Combines multiple text strings into one.

=CONCATENATE(A2, " ", B2)

2. LEFT Function

Extracts a given number of characters from the left side of a text string.

=LEFT(A2, 5)

3. RIGHT Function

Extracts a given number of characters from the right side of a text string.

=RIGHT(A2, 4)

4. MID Function

Extracts a given number of characters from the middle of a text string.

=MID(A2, 3, 5)

5. LEN Function

Returns the length of a text string.

=LEN(A2)

6. FIND Function

Finds the starting position of one text string within another (case-sensitive).

=FIND("apple", A2)

7. SEARCH Function

Finds the starting position of one text string within another (not case-sensitive).

=SEARCH("apple", A2)

8. SUBSTITUTE Function

Replaces existing text with new text in a string.

=SUBSTITUTE(A2, "old_text", "new_text")

9. UPPER Function

Converts text to uppercase.

=UPPER(A2)

10. LOWER Function

Converts text to lowercase.

=LOWER(A2)

11. PROPER Function

Capitalizes the first letter of each word in a text string.

=PROPER(A2)

12. TRIM Function

Removes all extra spaces from text except for single spaces between words.

=TRIM(A2)

Practical Application Example

Combining multiple text manipulation functions to clean and format data.

Original Text in Cell A2

  john doe123@example.COM

Formula in Cell B2

Combining:

TRIM to remove extra spaces
LOWER to convert all text to lowercase
PROPER to capitalize the first letter of each word

=PROPER(TRIM(LOWER(A2)))

Result in Cell B2

John Doe123@Example.com

This example demonstrates how to clean and format text to meet specific requirements using Excel text functions.

Intro to Logical Formulas in Excel

Logical Formulas

IF Function

Uses a condition to return one value if the condition is TRUE and another value if the condition is FALSE.

=IF(A1 > 10, "Greater than 10", "Less than or equal to 10")

AND Function

Returns TRUE if all conditions are TRUE, otherwise returns FALSE.

=AND(A1 > 5, B1 < 10)

OR Function

Returns TRUE if any condition is TRUE, otherwise returns FALSE.

=OR(A1 > 5, B1 < 10)

NOT Function

Reverses the value of its argument.

=NOT(A1 > 10)

Combining Logical Functions

Nested IF

Uses multiple IF statements to evaluate more than one condition.

=IF(A1 > 10, "Greater than 10", IF(A1 > 5, "Between 6 and 10", "5 or less"))

IF with AND

Checks multiple conditions within an IF statement.

=IF(AND(A1 > 5, A1 < 10), "Between 6 and 9", "Outside range")

IF with OR

Evaluates conditions using OR within an IF statement.

=IF(OR(A1 < 5, B1 > 20), "Condition met", "Condition not met")

Combining IF, AND, OR, NOT

Complex logical formula using a combination of functions.

=IF(AND(OR(A1 > 10, B1 < 5), NOT(C1 = 0)), "Complex Condition Met", "Complex Condition Not Met")

Practical Examples

Marking Pass/Fail

=IF(A1 >= 50, "Pass", "Fail")

Categorizing Data

=IF(A1 > 90, "Excellent", IF(A1 >= 75, "Good", IF(A1 >= 50, "Average", "Poor")))

Bonus Eligibility

=IF(AND(A1 >= 5, B1 > 20000), "Eligible for Bonus", "Not Eligible")

Use these formulas and combine them according to your specific needs to analyze data effectively in Excel.

Utilizing Date and Time Functions in Excel

1. TODAY and NOW Functions

Adding Current Date

=TODAY()

Adding Current Date and Time

=NOW()

2. DATE Function

Creating a Specific Date

=DATE(2023, 10, 5)  # Year, Month, Day

3. DATEVALUE Function

Converting a Date String to a Date Value

=DATEVALUE("10/05/2023")

4. TIME Function

Creating a Specific Time

=TIME(14, 30, 0)  # Hour, Minute, Second

5. TIMEVALUE Function

Converting a Time String to a Time Value

=TIMEVALUE("2:30 PM")

6. DAY, MONTH, YEAR Functions

Extracting Day from Date

=DAY(A1)  # Assuming A1 contains a date

Extracting Month from Date

=MONTH(A1)

Extracting Year from Date

=YEAR(A1)

7. HOUR, MINUTE, SECOND Functions

Extracting Hour from Time

=HOUR(A2)  # Assuming A2 contains a time

Extracting Minute from Time

=MINUTE(A2)

Extracting Second from Time

=SECOND(A2)

8. WEEKDAY Function

Finding the Day of the Week

=WEEKDAY(A1, 1)  # 1: Sun-Sat, 2: Mon-Sun, 3: Mon-Sun where Mon = 0

9. EOMONTH Function

Finding the End of the Month

=EOMONTH(A1, 0)  # "0" means the current month, "1" means next month

10. NETWORKDAYS Function

Calculating Workdays Between Two Dates

=NETWORKDAYS(A1, B1)  # A1: Start Date, B1: End Date

11. DATEDIF Function

Calculating Difference Between Dates

=DATEDIF(A1, B1, "D")  # "D" for days, "M" for months, "Y" for years

These functions and examples cover the practical usage of date and time functions within Excel. Apply them accordingly to manage dates and times effectively in your spreadsheets.

Part 6: Lookup and Reference Functions in Excel

VLOOKUP

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
```
=VLOOKUP(A2, B2:D10, 3, FALSE)
```

HLOOKUP

Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example:
```
=HLOOKUP(A2, B2:J2, 3, FALSE)
```

INDEX

Syntax: =INDEX(array, row_num, [col_num])
Example:
```
=INDEX(B2:D10, 3, 2)
```

MATCH

Syntax: =MATCH(lookup_value, lookup_array, [match_type])
Example:
```
=MATCH(A2, B2:B10, 0)
```

USING INDEX AND MATCH TOGETHER

Formula:

=INDEX(B2:D10, MATCH(A2, B2:B10, 0), 2)

OFFSET

Syntax: =OFFSET(reference, rows, cols, [height], [width])
Example:
```
=OFFSET(A1, 2, 3, 1, 1)
```

INDIRECT

Syntax: =INDIRECT(ref_text, [a1])
Example:
```
=INDIRECT("A" & B1)
```

COLUMN

Syntax: =COLUMN([reference])
Example:
```
=COLUMN(B2)
```

ROW

Syntax: =ROW([reference])
Example:
```
=ROW(A2)
```

LOOKUP

Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])
Example:
```
=LOOKUP(A2, B2:B10, C2:C10)
```

CHOOSE

Syntax: =CHOOSE(index_num, value1, [value2], ...)

Example:

=CHOOSE(2, "Apple", "Banana", "Cherry")

This guide should aid in utilizing Excel's lookup and reference functions efficiently for practical data analysis scenarios.

Part 7: Data Analysis with Pivot Tables

Creating a Pivot Table in Excel

Select Data Range
- Highlight the range of data you want to create a Pivot Table from.
Insert Pivot Table
- Go to Insert > Pivot Table.
Create PivotTable Dialog Box
- Choose whether you want the Pivot Table in a new worksheet or existing worksheet.
- Click OK.

Configuring Pivot Table Fields

Drag Fields to Areas
- Rows Area: Drag and drop the fields you want to analyze into the Rows area.
- Columns Area: Drag and drop fields here to compare data side by side.
- Values Area: Drag numeric fields here. By default, it will sum the data.
- Filters Area: Drag field(s) here to allow you to filter the data in the pivot table.

Example: Sales Data Analysis

Assume we have a sales dataset with Date, Salesperson, Region, and Revenue.

Step-by-Step Example

Insert Pivot Table
- Select data range.
- Click Insert > Pivot Table.
Set Up Fields
- Drag Salesperson to the Rows area.
- Drag Region to the Columns area.
- Drag Revenue to the Values area (ensure it shows Sum of Revenue).

Example Result Interpretation

Rows: Each row represents a Salesperson.
Columns: Each column represents a Region.
Values: Display the total revenue per Salesperson per Region.

Additional Features

Filter
- Drag Date to the Filters area to analyze sales performance over different periods.
Refreshing Data
- Right-click on the Pivot Table and select Refresh to update with new data.

Pivot Table Options

Summarize Values By: Change how values are calculated (Sum, Average, Count).
Show Values As: Change the way values are displayed (Percentage, Running Total).

Formatting Pivot Table

Design
- Go to the Design tab to customize the appearance of the Pivot Table.
Number Formatting
- Right-click a value in the Pivot Table, select Value Field Settings, and choose Number Format.

By following these steps, you can effectively analyze your data with Pivot Tables in Excel.

Visualizing Data with Charts and Graphs in Excel

1. Inserting a Chart

Select the data range you wish to chart.
Go to the Insert tab.
Choose the desired chart type within the Charts group (e.g., Column, Line, Pie).

2. Formatting the Chart

To format different chart elements:
- Click on the chart to select it.
- Use Chart Tools tabs (Design, Format) to customize.

2.1 Change Chart Title

Click on the chart title.
Type in your desired title.

2.2 Add Data Labels

Click on the chart.
Go to Chart Tools Design > Add Chart Element > Data Labels.
Choose the position for data labels.

2.3 Customize Axis Titles

Go to Chart Tools Design > Add Chart Element > Axis Titles.
Choose Primary Horizontal for x-axis, Primary Vertical for y-axis.
Edit the axis titles directly by clicking on them.

3. Changing Chart Type

Select the chart.
Go to Chart Tools Design > Change Chart Type.
Choose a new chart type and click OK.

4. Moving the Chart

Click on the chart.
Drag it to the desired location within the worksheet.

5. Resizing the Chart

Click on the chart.
Drag one of the sizing handles (corners or sides) to resize.

6. Adding Trendlines

Select the data series on the chart.
Go to Chart Tools Design > Add Chart Element > Trendline.
Choose the trendline type (e.g., Linear, Exponential).

7. Using Chart Templates

After customizing, right-click the chart.
Select Save as Template, name the template, and save.
Next time you create a chart, use this template via Chart Tools Design > Change Chart Type > Templates.

8. Using Sparklines for Quick Visuals

Select the cell range where sparklines will be displayed.
Go to Insert > Sparklines group.
Choose Line, Column, or Win/Loss.
Select the data range you want to visualize and click OK.

Practical Examples

Example 1: Insert a Column Chart

Select A1:B5 (range with sales data).
Go to Insert > Column or Bar Chart > Clustered Column.

Example 2: Add Data Labels

Click on the inserted column chart.
Chart Tools Design > Add Chart Element > Data Labels > Outside End.

Conclusion

Following these steps will help you effectively visualize data in Excel with charts and graphs. Adjusting and formatting charts ensure they accurately represent your data, making your analysis clearer and more presentable.

Data Validation Techniques in Excel

Step-by-Step Implementation

Select the Cells to Apply Validation
- Click and drag over the cells where you want to apply data validation.
Open Data Validation Dialog Box
- Go to the Data tab on the Excel Ribbon.
- Click on the Data Validation button in the Data Tools group.
Set Validation Criteria
- Under the Settings tab, specify the validation criteria:
  - Allow: Choose the type of data (e.g. Whole number, Decimal, List, Date, Time, Text length, Custom).
  - Data: Specify the condition (e.g. between, not between, equal to, not equal to, greater than, less than).
  - Minimum/Maximum/Specific Values: Set the range or specific value.
Input Message Tab (Optional)
- You can configure a message that appears when the user selects the cell:
  - Check Show input message when cell is selected.
  - Enter a Title and Input message.
Error Alert Tab
- Define what happens when invalid data is entered:
  - Check Show error alert after invalid data is entered.
  - Choose a Style (Stop, Warning, Information).
  - Enter a Title and Error message.

Examples of Different Data Validations:

a. Whole Number Validation

- Allow: Whole number
- Data: between
- Minimum: 1
- Maximum: 100

b. Date Validation

- Allow: Date
- Data: between
- Start date: 01/01/2023
- End date: 12/31/2023

c. List Validation

- Allow: List
- Source: Apple, Orange, Banana

Custom Validation (e.g., Only Allow Emails)
- Use the Custom option to create more complex validations:
```
- Allow: Custom
- Formula: =ISNUMBER(MATCH("*@*.com", A1, 0))
```
Apply and Test
- Click OK to apply the validation.
- Test by trying to enter invalid data to ensure the validation rules work correctly.

Additional Tips

Copy the cell with the validation rule and paste it to other cells to replicate the validation.
Use named ranges for list sources to make management easier.
Regularly review and update validation rules as your data requirements change.

Advanced Tips and Tricks for Efficient Data Handling in Excel

Using Array Formulas

Array formulas can perform multiple calculations on one or more items in an array. An array formula can return either multiple results or a single result.

Example: Sum of products

=SUM(A2:A10 * B2:B10)

Press Ctrl + Shift + Enter, not just Enter, to commit this formula.

Dynamic Named Ranges

To create a dynamic named range that automatically adjusts in size:

Go to Formulas -> Name Manager -> New

Enter a name and in the Refers to box, enter:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Removing Duplicates Efficiently

Use Remove Duplicates found under the Data tab.

Select the data range.
Go to Data -> Remove Duplicates.

Quick Analysis Tool

To quickly analyze data:

Highlight your data range.
Click on the Quick Analysis tool that pops up at the bottom right.
Choose from options like Formatting, Charts, Totals, Tables, Sparklines.

Using Slicers for Filtering

Insert a Table: Select your data -> Insert -> Table.
With the table selected, go to Table Tools Design -> Insert Slicer.
Choose the columns you want slicers for.

Flash Fill for Quick Data Entry

Flash Fill can automatically fill your data when it senses a pattern.

Start typing in a cell.
Use Ctrl + E after entering the desired pattern for the first entry.

Advanced Pivot Table Filters

To filter data in a Pivot Table:

Click on the Pivot Table.
Use the Filters area in the PivotTable Field List.
Drag fields to the Filters section to add filters.

Conditional Formatting with Formulas

Select the range you want to conditionally format.
Go to Home -> Conditional Formatting -> New Rule.
Choose Use a formula to determine which cells to format.
Enter a formula, e.g.,
```
=A1>100
```
Set your formatting options.

Utilizing Excel Tables for Structured Data

Select your data range.
Go to Insert -> Table.
Use Table Tools Design to manage Table properties like Total Row.

INDIRECT Function for Dynamic References

Example: Use INDIRECT to switch between sheets:

=SUM(INDIRECT("Sheet" & B1 & "!A1:A10"))

Where B1 contains the sheet number/name.

Data Consolidation from Multiple Ranges

Go to the Data tab.
Click Consolidate in the Data Tools group.
Choose your function (e.g., Sum).
Add your data ranges.

These advanced tips will streamline your data handling process and make working with Excel more efficient.

Building a VBA-Based Task Automation Tool

« Older Entries