Excel Basics

1. Excel Interface Overview

• Details: Familiarize with Excel’s main elements – the Ribbon, Quick Access Toolbar
(QAT), formula bar, sheet tabs, and status bar.
• Example: Explain how to use the Ribbon tabs like Home, Insert, Page Layout, etc.,
and where to find commands such as Copy, Paste, and AutoSum.

2. Workbook vs. Worksheet

• Details: Understand the difference between an Excel file (workbook) and individual
sheets (worksheets).
• Example: Create a workbook with multiple sheets for different monthly reports. Each
sheet is a unique space within the same workbook.

3. Creating a New Workbook

• Details: Learn to create new workbooks from scratch or templates.
• Example: Use File > New > Blank Workbook or select a pre-made template like
“Budget” to start with a layout.

4. Saving and Closing Workbooks

• Details: Save work periodically to prevent data loss. Explore Save As for creating
different versions.
• Example: File > Save As allows naming and choosing file formats like .xlsx, .xls, or
.pdf.

5. Opening Existing Workbooks

• Details: Open existing files, especially in shared work environments.
• Example: File > Open > Recent provides access to recently used files, while Browse
opens files from any location.
6. Navigating the Ribbon and Toolbar
• Details: Use the Ribbon for accessing tools and commands, organized into tabs.
• Example: In the Home tab, find frequently used options like Font, Alignment, and
Number Formatting.

7. Using Quick Access Toolbar (QAT)

• Details: Customize the QAT for quick access to favorite tools.
• Example: Add commonly used commands like Save, Undo, and Redo by right-
clicking any command and selecting “Add to Quick Access Toolbar.”

8. Introduction to Cells, Rows, and Columns

• Details: Cells are individual data containers; rows run horizontally and columns
vertically.
• Example: Refer to cell “B2” (column B, row 2) for data entry or formulas.

9. Selecting Cells and Ranges

• Details: Select single cells, entire rows/columns, or ranges of cells.
• Example: Click and drag from “A1” to “C3” to select a range and apply formatting or
calculations.

10. Data Entry Basics (Text, Numbers)

• Details: Enter text and numerical data directly into cells.
• Example: Type “Inventory” in cell A1 and “100” in cell B1 to track stock.

11. Editing Cell Content

• Details: Double-click a cell or use the formula bar to edit data within a cell.
• Example: Change the text in cell A1 from “Inventory” to “Stock Items” to make it
clearer.
12. Deleting Cell Content
• Details: Use the delete key or right-click options to clear cell content.
• Example: Pressing Delete clears the data in a cell but keeps the formatting.

13. Using Undo and Redo

• Details: Undo reverses recent actions; Redo re-applies them.
• Example: Press Ctrl + Z to undo an action, and Ctrl + Y to redo it.

14. Basic Cell Formatting (Font, Size)

• Details: Customize font type, size, color, and style (bold, italic).
• Example: Select cell A1, go to Home > Font group, and increase the font size to 14
to make headers stand out.

15. Text Alignment in Cells

• Details: Align text horizontally and vertically within cells.
• Example: Center-align text in cell A1 for a neater look by selecting Home >
Alignment > Center.

16. Applying Bold, Italic, and Underline

• Details: Add emphasis using bold, italic, or underline styles.
• Example: Highlight a column header and click Bold in the Font group for readability.

17. Using Borders in Cells

• Details: Add borders around cells or ranges to define sections visually.
• Example: Select range A1

and apply a thick outside border to highlight a summary area.

18. Applying Cell Background Colors
• Details: Use fill color to make specific cells stand out.
• Example: Apply a light blue background to header cells to differentiate them.

19. Merging and Centering Cells

• Details: Combine multiple cells into one for headings or labels.
• Example: Merge cells A1 to D1 and center the text for a title section across columns.

20. AutoFit Columns and Rows

• Details: Adjust cell width/height automatically based on content.
• Example: Double-click the edge of column A to fit the widest cell in that column.

21. Resizing Columns and Rows Manually

• Details: Adjust column width and row height manually for better data display.
• Example: Hover over the line between columns A and B in the header, click, and
drag to widen column A.

22. Inserting New Rows and Columns

• Details: Add rows above or columns to the left of the selected cell.
• Example: Right-click on row 3, select “Insert” to add a new row above row 3.

23. Deleting Rows and Columns

• Details: Remove unwanted rows or columns from a worksheet.
• Example: Right-click on column B, select “Delete” to remove the entire column.

24. Freeze Panes for Easy Scrolling

• Details: Lock specific rows or columns to keep headers visible while scrolling.
• Example: Select cell A2, then View > Freeze Panes to keep the first row visible as
you scroll.
25. Splitting the Worksheet View
• Details: Split the worksheet into two sections to view distant parts simultaneously.
• Example: Select cell D5, then View > Split to create a four-way split, making
navigation easier.

26. Hiding and Unhiding Rows/Columns

• Details: Temporarily hide rows or columns to simplify data views.
• Example: Right-click on row 4, choose “Hide”; to reveal, select surrounding rows,
right-click, and select “Unhide.”

27. Basic Number Formatting (Currency, Percentage)

• Details: Format numbers as currency, percentages, or decimals.
• Example: Select a range of sales data, go to Home > Number Format and choose
“Currency” to standardize values.

28. Formatting Dates and Times

• Details: Use specific date and time formats for clarity and consistency.
• Example: Select cells with dates, choose Short Date or Long Date from the Home >
Number Format dropdown.

29. Using Format Painter

• Details: Copy formatting from one cell to others quickly.
• Example: Format cell A1, select it, click Format Painter, then click another cell to
apply the same format.

30. Copying and Pasting Data

• Details: Transfer data from one location to another within the same sheet or different
sheets.
• Example: Select cell A1, copy (Ctrl+C), and paste (Ctrl+V) in cell B1 to duplicate
data.
31. Cutting and Pasting Data
• Details: Move data within the same sheet or across sheets.
• Example: Cut data from cell B2 (Ctrl+X) and paste it into cell C3 (Ctrl+V).

32. Using Paste Special Options

• Details: Paste only specific data attributes like values, formats, or formulas.
• Example: Copy a cell, right-click in the target cell, select “Paste Special” > “Values”
to paste only the value.

33. Basic Formulas: SUM Function

• Details: Use SUM to add a range of numbers.
• Example: =SUM(A1

) adds values in cells A1 through A5.

34. Using the AVERAGE Function

• Details: Calculate the average of a range of numbers.
• Example: =AVERAGE(B1

) finds the mean of values in cells B1 to B5.

35. MIN and MAX Functions

• Details: Find the minimum or maximum values in a range.
• Example: =MIN(C1

) returns the smallest value in the range, while =MAX(C1

) returns the largest.

36. Using AutoSum for Quick Calculations
• Details: Quickly add up a column or row of numbers with AutoSum.
• Example: Click on a cell below a column of numbers, then click Home > AutoSum to
sum the entire column.

37. Introduction to Cell References (Relative)

• Details: Understand how Excel adjusts formulas based on the cell location.
• Example: If =A1+B1 is in cell C1, copying it to C2 changes the formula to =A2+B2.

38. Understanding Absolute References ($)

• Details: Lock specific cells in formulas by using the $ symbol.
• Example: =A1*$B$1 keeps B1 fixed when copied, while A1 will adjust based on the
cell’s new location.

39. Basic Fill Handle Techniques

• Details: Use the fill handle to duplicate or continue patterns in data.
• Example: Enter “1” in cell A1, “2” in cell A2, then drag the fill handle to continue the
sequence.

40. Creating Simple Charts (Column, Bar)

• Details: Visualize data by creating basic column and bar charts.
• Example: Select a range, go to Insert > Chart and choose a column or bar chart to
represent your data visually.

41. Adding Titles to Charts

• Details: Improve chart readability by adding clear titles.
• Example: Select a chart, go to Chart Design > Add Chart Element > Chart Title, and
enter a descriptive title like “Monthly Sales.”
42. Basic Chart Formatting Options
• Details: Customize the look of charts using styles, colors, and layouts.
• Example: Click on a chart, go to Chart Design to apply different styles and color
schemes that fit your data’s theme.

43. Adjusting Chart Layout and Styles

• Details: Modify the chart layout and style to present data more effectively.
• Example: Use Quick Layout under Chart Design to add elements like data labels,
axis titles, and legend positions.

44. Renaming Worksheets

• Details: Give meaningful names to worksheet tabs for easy navigation.
• Example: Right-click on Sheet1, select Rename, and change it to “January Sales” to
reflect the content.

45. Adding and Deleting Worksheets

• Details: Create new sheets or delete unnecessary ones within a workbook.
• Example: Click the “+” icon at the bottom left to add a new sheet, or right-click a
sheet tab and select Delete.

46. Moving and Copying Worksheets

• Details: Rearrange sheets within the workbook or copy them to other workbooks.
• Example: Right-click a sheet tab, select Move or Copy, and choose where to place
the duplicate sheet.

47. Changing Worksheet Tab Colors

• Details: Color-code tabs for organization and quick access.
• Example: Right-click on a worksheet tab, select Tab Color, and choose a color to
indicate categories or priorities.
48. Basic Sorting (Ascending, Descending)
• Details: Organize data by sorting columns alphabetically or numerically.
• Example: Select a column with names, go to Data > Sort A-Z for ascending
alphabetical order.

49. Sorting Multiple Columns

• Details: Sort data based on more than one column (e.g., by department and name).
• Example: Select your data range, go to Data > Sort, and add sorting levels such as
Department first, then Name.

50. Filtering Data in Excel

• Details: Use filters to display only rows that meet specific criteria.
• Example: Go to Data > Filter, click the dropdown arrow on the “Status” column, and
filter to show only “Completed” tasks.

51. Removing Filters

• Details: Clear filters to view the full dataset.
• Example: Data > Clear in the Filter section to remove all filters and restore the
complete data view.

52. Applying Basic Conditional Formatting

• Details: Automatically format cells based on their values (e.g., highlighting low
inventory).
• Example: Select cells, go to Home > Conditional Formatting > Highlight Cell Rules >
Less Than, and set criteria for highlighting.

53. Using Predefined Conditional Formatting Rules

• Details: Apply built-in rules for common conditions like top/bottom values.
• Example: Home > Conditional Formatting > Top/Bottom Rules lets you highlight the
top 10% of sales data.
54. Removing Conditional Formatting
• Details: Clear all or specific conditional formats.
• Example: Select a range, go to Home > Conditional Formatting > Clear Rules >
Clear Rules from Selected Cells.

55. Basic Data Validation (Lists)

• Details: Limit data entry options by creating dropdown lists.
• Example: Select a cell, go to Data > Data Validation, choose “List,” and enter
options like “Yes, No” to create a dropdown.

56. Adding Comments to Cells

• Details: Add comments to provide additional context or explanations.
• Example: Right-click a cell, select New Comment, and type a note about why certain
data is marked as “High Priority.”

57. Navigating Between Worksheets

• Details: Switch easily between multiple worksheets in a workbook.
• Example: Click sheet tabs directly or use Ctrl + Page Up/Page Down to move
between sheets.

58. Using Page Layout View

• Details: Preview how the worksheet will look when printed.
• Example: View > Page Layout shows headers, margins, and page breaks for print
settings.

59. Setting Print Area

• Details: Define a specific area of the sheet to be printed.
• Example: Select a range, go to Page Layout > Print Area > Set Print Area to print
only selected data.
60. Adjusting Page Orientation (Portrait, Landscape)
• Details: Change the print orientation to fit data better on the page.
• Example: Page Layout > Orientation lets you switch between portrait and landscape
to better display wide tables.

61. Setting Margins for Printing

• Details: Adjust margins to control the spacing between data and page edges for
printing.
• Example: Go to Page Layout > Margins and choose Narrow for more data per page,
or customize with Custom Margins.

62. Adding Headers and Footers

• Details: Include headers and footers with information like titles, dates, or page
numbers.
• Example: Go to Insert > Header & Footer, add your company name in the header,
and a page number in the footer.

63. Scaling a Worksheet to Fit on One Page

• Details: Resize content to fit a single printed page.
• Example: Page Layout > Scale to Fit, set Width and Height to “1 page” to ensure all
data fits on one page.

64. Adding Page Breaks

• Details: Insert manual page breaks for better control over print layout.
• Example: Click on a row, go to Page Layout > Breaks > Insert Page Break to start a
new page from that row.

65. Printing Row and Column Headers

• Details: Keep row and column headers visible on every printed page.
• Example: Go to Page Layout > Print Titles, select rows or columns you want to
repeat, and print the sheet.
66. Spell Check in Excel
• Details: Check for spelling errors in text entries.
• Example: Review > Spelling checks the sheet for typos, helpful in presentations or
reports.

67. Using Find and Replace

• Details: Search for and replace specific text or values.
• Example: Ctrl + H opens Find and Replace to replace all occurrences of “Pending”
with “In Progress”.

68. Basic Error Checking

• Details: Detect and correct common errors in formulas.
• Example: If a cell shows “#DIV/0!”, hover over it for suggestions to fix the error, like
dividing by a non-zero number.

69. Introduction to Basic Keyboard Shortcuts

• Details: Learn common shortcuts like Ctrl + C (Copy), Ctrl + V (Paste), Ctrl + Z
(Undo).
• Example: Practice shortcuts to save time, such as Alt + Enter to add a line within a
cell.

70. Using AutoFill for Series and Patterns

• Details: AutoFill allows you to quickly extend sequences or patterns.
• Example: Type “Monday” in cell A1, drag the fill handle to auto-complete the week’s
days across adjacent cells.

71. Inserting Basic Hyperlinks

• Details: Link to other files, websites, or cells within the workbook.
• Example: Insert > Link allows you to create a hyperlink to “www.example.com” or to
a specific cell.
72. Basic Protection for Worksheets
• Details: Protect worksheets to prevent accidental edits.
• Example: Go to Review > Protect Sheet, set a password, and select options for what
can still be edited.

73. Protecting Cells with Data Validation

• Details: Restrict data entry to specific formats or values using validation.
• Example: Set data validation to only allow dates between “01/01/2024” and
“12/31/2024” for a date entry cell.

74. Basic File Password Protection

• Details: Password-protect entire workbooks for security.
• Example: File > Info > Protect Workbook > Encrypt with Password lets you add a
password to the workbook.

75. Using COUNT and COUNTA Functions

• Details: COUNT counts numeric cells, COUNTA counts all non-empty cells.
• Example: =COUNT(A1

) counts cells with numbers; =COUNTA(A1

) counts all non-empty cells.

76. Understanding the IF Function

• Details: The IF function returns one value if a condition is true and another if false.
• Example: =IF(B1>=50, "Pass", "Fail") returns “Pass” if B1 is 50 or more, and “Fail”
otherwise.

77. Using the CONCATENATE Function

• Details: Combine multiple text strings into one cell.
• Example: =CONCATENATE(A1, " ", B1) combines text from cells A1 and B1 with a
space in between.
78. Introduction to the NOW Function
• Details: Display the current date and time.
• Example: =NOW() displays the date and time the formula is entered or refreshed.

79. Introduction to the TODAY Function

• Details: Display today’s date, updating automatically each day.
• Example: =TODAY() displays the current date, useful for tracking daily updates or
reports.

80. Using the LEFT, RIGHT, and MID Functions for Text
• Details: Extract specific parts of text strings.
• Example: =LEFT(A1, 3) returns the first three characters of the text in cell A1;
=RIGHT(A1, 4) returns the last four.

81. Using the TRIM Function to Clean Text

• Details: Remove extra spaces from text strings, keeping only single spaces between
words.
• Example: =TRIM(A1) removes leading, trailing, and extra spaces in the text from cell
A1.

82. Basic Formatting: Font Styles (Bold, Italic, Underline)

• Details: Change text appearance using bold, italic, and underline styles.
• Example: Select the text, and use Home > Font to click Bold (B), Italic (I), or
Underline (U) to enhance readability.

83. Basic Formatting: Cell Borders

• Details: Add borders to cells for clearer data organization.
• Example: Select a range, go to Home > Font > Borders to choose a border style,
such as a thick outline around a table.
84. Applying Cell Shading/Background Colors
• Details: Use fill colors to highlight important data.
• Example: Select a range, click on the paint bucket icon in the Home > Font group,
and choose a color to fill the cells.

85. Using the Data Series for Linear Growth

• Details: Create a series of numbers or dates that increase in a consistent manner.
• Example: Enter “1” in cell A1 and “2” in A2, select both, then drag the fill handle
down to fill the series automatically.

86. Using the MONTH and YEAR Functions

• Details: Extract the month or year from a date.
• Example: =MONTH(A1) returns the month from the date in cell A1; =YEAR(A1)
returns the year.

87. Using the TEXT Function for Formatting

• Details: Convert numbers to text in a specific format.
• Example: =TEXT(A1, "0.00") converts the number in A1 to text with two decimal
places.

88. Basic Graphical Objects: Inserting Shapes

• Details: Add shapes to enhance the visual presentation of data.
• Example: Insert > Shapes lets you choose a rectangle, circle, or arrow to annotate
or highlight parts of the worksheet.

89. Grouping Rows or Columns

• Details: Collapse or expand rows or columns for better data organization.
• Example: Select rows 2-5, go to Data > Group, which allows you to collapse or
expand them with a click.
90. Using the Slicer for Filtering Data
• Details: Visual filters for tables and PivotTables to slice data dynamically.
• Example: Insert a Slicer from a table to filter data quickly by selecting specific
categories like “Sales Regions.”

91. Inserting SmartArt for Visual Representation

• Details: Create diagrams and organizational charts using SmartArt.
• Example: Insert > SmartArt allows you to choose a graphic to represent processes,
hierarchies, or relationships visually.

92. Creating a Basic Table

• Details: Convert a data range into an Excel table for enhanced functionality.
• Example: Select your data, then Insert > Table creates a structured table with
sorting and filtering capabilities.

93. Adding Total Row in a Table

• Details: Quickly summarize data in a table with a total row.
• Example: With a table selected, check the Total Row option in Table Design to add
sums or averages at the bottom.

94. Using the VLOOKUP Function

• Details: Search for a value in the first column of a table and return a value in the
same row from a specified column.
• Example: =VLOOKUP("Item1", A2

, 2, FALSE) looks for “Item1” in the first column and returns the corresponding value
from the second column.
95. Using the HLOOKUP Function
• Details: Similar to VLOOKUP, but searches for a value in the first row and returns a
value from a specified row.
• Example: =HLOOKUP("Q1", A1

, 2, FALSE) searches for “Q1” in the first row and returns the corresponding value
from the second row.

96. Creating a Drop-Down List Using Data Validation

• Details: Limit entries to specific options through a drop-down list.
• Example: Go to Data > Data Validation, choose “List,” and specify values like “Yes,
No” to create a dropdown in a cell.

97. Understanding and Using the SUMIF Function

• Details: Calculate the sum of a range based on a specific criterion.
• Example: =SUMIF(A1

, ">50", B1

) sums values in B1

where corresponding A1

values are greater than 50.

98. Understanding and Using the COUNTIF Function

• Details: Count the number of cells that meet a specified condition.
• Example: =COUNTIF(A1

, "Passed") counts how many times “Passed” appears in the range A1

99. Using the AVERAGEIF Function
• Details: Calculate the average of a range based on a criterion.
• Example: =AVERAGEIF(A1

, "<100", B1

) finds the average of values in B1

where corresponding A1

values are less than 100.

100. Saving Workbooks in Different Formats

• Details: Save Excel files in various formats, such as .xlsx, .xls, .csv, or .pdf.
• Example: Go to File > Save As, choose your preferred format from the dropdown list
to save the workbook accordingly.