Advanced MS-Excel notes

Video Links-

1. Cell referencing - https://youtu.be/LFIykJmL4M8?si=CerHN91Pdokcy3TP

2. Sorting- https://youtu.be/m97Rbqs6ico?si=TA9EbpZy-wfdtwNY

3. Page setup- https://youtu.be/DPh9ZPzExTI?si=aX1YBU1zlBA2MlBR

4. Text functions- https://youtu.be/4mwaiA4rL4o?si=7tEAEm_Bhm4PZ5Gf

5. Logical functions- https://youtu.be/sidOELWrsMQ?si=pyKBP7tQU2aUkOL5

6. VLOOKUP- https://youtu.be/Jwo5Ae7tH_I?si=y1Y8EU4U958SCFbL

7. HLOOKUP- https://youtu.be/eGNVhJTWoE0?si=mJpamqzAgYfe3uOz

8. Hyperlink- https://youtu.be/Iq8yiUNtNqI?si=K2TVAlCqoXvqaQLP

9. Sparkline- https://youtu.be/1PvrvyZuzgo?si=JTZc5KL0_f0Zgauz

10. What-if analysis- https://youtu.be/FXaV4lckino?si=tvP6UddkbM7O0pZo

11. Pivot tables- https://youtu.be/4PWVFBiFVVU?si=bCpaY7ZYXprwyQvk

https://youtu.be/Y72CeUEn_Vc?si=LsqcicqXdhWVzed0

https://youtu.be/FKD4KFHBN4M?si=fasir11Es3-vKvd0

12. Data validation- https://youtu.be/nMxl1_NAcxc?si=yqukaotn1U9qT3At

13. Pivot charts- https://youtu.be/mc7xO8F8Pj8?si=BrsoP6kR79oL43gx

 Notes

Cell Referencing in MS Excel

1. Types of Cell References:
 Relative Reference: Adjusts based on the cell’s position when copied (e.g., A1).
 Absolute Reference: Remains constant no matter where it’s copied (e.g., $A$1).
 Mixed Reference: Combines relative and absolute references (e.g., A$1 or $A1).
2. Using Cell References:
 Formulas: Use cell references to create dynamic formulas (e.g., =A1+B1).
 Copying Formulas: Understand how references adjust when formulas are copied
across cells.
3. Reference Other Sheets:
 Single Sheet Reference: SheetName!Cell (e.g., Sheet2!A1).
 Across Multiple Sheets: SheetName:SheetName!Cell (e.g., Sheet1:Sheet3!A1).
4. Named Ranges:
 Define Name: Create easy-to-remember names for specific cell ranges (e.g.,
=SalesData for A1:A10).
 Use in Formulas: Simplifies complex formulas (e.g., =SUM(SalesData)).
Inserting Functions in MS Excel
1. Accessing Functions:
 Function Wizard: Click on the fx button next to the formula bar.
 Insert Function Dialog: Use Insert > Function or Formulas > Insert Function.
2. Common Functions:
 SUM: Adds values (e.g., =SUM(A1:A10)).
 AVERAGE: Calculates the average of values (e.g., =AVERAGE(B1:B10)).
 IF: Performs a logical test (e.g., =IF(A1>10, "Yes", "No")).
 VLOOKUP: Searches for a value in a table (e.g., =VLOOKUP(A1, B1:D10, 2,
FALSE)).
 COUNT: Counts the number of cells that contain numbers (e.g.,
=COUNT(A1:A10)).
3. Formula AutoComplete:
 Typing Formulas: Start typing a function, and Excel will suggest possible
functions.
 Tooltip Help: Hover over a function name to see its syntax and arguments.
4. Nested Functions:
 Combining Functions: Use one function inside another (e.g., =IF(A1>10,
SUM(B1:B10), 0)).
Page Setup in MS Excel
1. Accessing Page Setup:
 Page Layout Tab: Use options in the Page Layout tab.
  File Menu: Select File > Print or Page Setup for print options.
2. Key Settings:
 Orientation: Choose between Portrait and Landscape.
 Size: Set the paper size (e.g., Letter, A4).
 Margins: Adjust the top, bottom, left, and right margins.
 Scaling: Fit the sheet to one page wide or tall.
3. Headers and Footers:
 Insert Headers/Footers: Use Insert > Text > Header & Footer.
 Customizing: Add elements like page numbers, date, file path, etc.
4. Print Area:
 Setting Print Area: Select the range and set it as the print area (Page Layout >
Print Area > Set Print Area).
 Clearing Print Area: Use Clear Print Area to remove the print area settings.
5. Page Breaks:
 Inserting Page Breaks: Use Page Layout > Breaks > Insert Page Break.
 Managing Page Breaks: Drag the page break lines or use Remove Page Break.
6. Sheet Options:
 Gridlines and Headings: Choose whether to print gridlines and row/column
headings (Page Layout > Sheet Options).
 Print Titles: Set rows or columns to repeat on every printed page (Page Layout >
Print Titles).

Conditional Formatting in MS Excel

1. Purpose:
 Highlight cells based on their values to visualize data patterns.
2. Accessing Conditional Formatting:
 Go to the Home tab, then click Conditional Formatting.
3. Types of Conditional Formatting:
 Highlight Cell Rules: Format cells based on criteria like greater than, less than,
between, etc.
 Top/Bottom Rules: Format cells with the highest or lowest values.
 Data Bars: Visual bars inside cells indicating their value.
 Color Scales: Apply a color gradient based on cell values.
 Icon Sets: Add icons based on cell values.
4. Creating Custom Rules:
 Select New Rule from the Conditional Formatting menu.
 Choose a rule type and define the formatting criteria.
5. Managing Rules:
 Use Manage Rules to edit, delete, or prioritize multiple rules.
Hyperlinks in MS Excel
1. Purpose:
 Link to websites, email addresses, or other cells and files.
2. Inserting a Hyperlink:
 Using the Menu: Select the cell, then go to Insert > Hyperlink.
 Right-Click Method: Right-click the cell and choose Hyperlink.
3. Types of Hyperlinks:
 Existing File/Web Page: Link to an external file or URL.
 Place in This Document: Link to another cell or sheet in the same workbook.
 Create New Document: Link that creates a new Excel file.
 Email Address: Open an email client with a predefined address.
4. Editing/Removing Hyperlinks:
 Right-click the hyperlink and select Edit Hyperlink or Remove Hyperlink.
Sparklines in MS Excel
1. Purpose:
 Provide a compact, visual representation of data trends within a single cell.
2. Types of Sparklines:
 Line: Shows trends over time.
 Column: Displays individual values.
 Win/Loss: Highlights positive and negative values.
3. Inserting Sparklines:
 Select the data range, go to Insert > Sparklines, choose the type, and specify the
location range.
4. Customizing Sparklines:
 Design Tab: Adjust sparkline color, style, and markers.
 Axis Options: Set minimum and maximum values for better context.
VLOOKUP in MS Excel
1. Purpose:
 Look up a value in a table and return a corresponding value from another
column.
2. Syntax:
 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
3. Arguments:
 lookup_value: The value to search for.
 table_array: The range containing the data.
 col_index_num: The column number in the table from which to retrieve the
value.
 range_lookup: TRUE for an approximate match, FALSE for an exact match.
4. Example:
 =VLOOKUP(A2, B2:D10, 3, FALSE) searches for the value in A2 within the range
B2
and returns the value from the third column in the range.
 HLOOKUP in MS Excel
1. Purpose:
 Look up a value in a table and return a corresponding value from another row.
2. Syntax:
 =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
3. Arguments:
 lookup_value: The value to search for.
 table_array: The range containing the data.
 row_index_num: The row number in the table from which to retrieve the value.
 range_lookup: TRUE for an approximate match, FALSE for an exact match.
4. Example:
 =HLOOKUP(A1, B1:H5, 3, FALSE) searches for the value in A1 within the range B1
and returns the value from the third row in the range.

Goal Seek in MS Excel

1. Purpose:
 Determine the input value needed to achieve a specific goal or result in a
formula.
2. Accessing Goal Seek:
 Go to the Data tab, then click on What-If Analysis and select Goal Seek.
3. Using Goal Seek:
 Set Cell: The cell containing the formula.
 To Value: The desired result.
 By Changing Cell: The input cell that Goal Seek will adjust.
4. Example:
 If you want to find out what sales amount will yield a total profit of $10,000, set
the profit cell as Set Cell, enter 10000 in To Value, and specify the sales amount
cell as By Changing Cell.
Pivot Tables in MS Excel
1. Purpose:
 Summarize, analyze, explore, and present data in a table format.
2. Creating a Pivot Table:
 Select the data range, go to Insert > PivotTable, and choose the location for the
Pivot Table.
3. Pivot Table Components:
 Rows: Fields that will be displayed as rows.
 Columns: Fields that will be displayed as columns.
 Values: Fields that will be calculated (e.g., sum, average).
 Filters: Fields that can be used to filter data.
4. Customizing Pivot Tables:
 Value Field Settings: Change the calculation type (sum, count, average).
 Group Data: Group dates or numerical values.
 Refresh Data: Update the Pivot Table when the source data changes (Analyze >
Refresh).
Subtotal in MS Excel
1. Purpose:
 Calculate subtotals and grand totals in a list or table.
2. Creating Subtotals:
 Sort the data by the column you want to subtotal.
 Go to Data > Subtotal.
 Choose the column to subtotal, the summary function (e.g., sum, count), and the
columns to be subtotaled.
3. Options in Subtotal:
 Replace Current Subtotals: Replace existing subtotals with new ones.
 Page Break Between Groups: Insert a page break between groups.
 Summary Below Data: Display subtotals below the data.
4. Removing Subtotals:
 Go to Data > Subtotal and click Remove All.
Pivot Charts in MS Excel
1. Purpose:
 Create a visual representation of data summarized in a Pivot Table.
2. Creating a Pivot Chart:
 Create a Pivot Table first.
 Select the Pivot Table, go to Insert > PivotChart, and choose the chart type.
3. Customizing Pivot Charts:
 Chart Elements: Add or remove elements like title, legend, data labels.
 Chart Styles: Change the chart's design and style.
 Filter Data: Use the Pivot Table filters to change the data displayed in the chart.
4. Refreshing Pivot Charts:
 Update the Pivot Chart when the Pivot Table data changes (Analyze > Refresh).
Auditing Tools in MS Excel
1. Purpose:
 Trace and check formulas to ensure accuracy and correctness.
2. Formula Auditing Tools:
 Trace Precedents: Show cells that affect the value of the selected cell (Formulas
> Trace Precedents).
 Trace Dependents: Show cells affected by the selected cell (Formulas > Trace
Dependents).
 Remove Arrows: Remove tracing arrows (Formulas > Remove Arrows).
3. Error Checking:
  Error Checking: Check for common formula errors (Formulas > Error Checking).
 Evaluate Formula: Step through each part of a formula to understand how it
works (Formulas > Evaluate Formula).
4. Watch Window:
 Monitor important cells and their values without scrolling (Formulas > Watch
Window).
5. Show Formulas:
 Display all formulas in the worksheet instead of their results (Formulas > Show
Formulas).