Excel Worksheets Basics – A Beginner’s Guide

This is an overview.

What Is an Excel Worksheet?

An Excel worksheet is a grid-based document used for organizing and analyzing numerical data.

The worksheet consists of rows and columns, forming cells where users can input and manipulate data.

Each intersection of a row and a column is called a cell, and each cell can contain text, numbers, formulas, or functions.

Users can perform various calculations, create lists or charts, and organize data in a structured manner.

How to Create an Excel Worksheet

• Open Microsoft Excel and select Blank workbook.

• The blank Excel worksheet is showcased as Sheet1.

How to Add New Worksheets in Your Workbook

• Press the plus (+) symbol at the bottom.

• A new sheet will open.

Note: You can insert a new worksheet by Pressing Shift + F11.

How to Rename a Worksheet

• Right-click the sheet name >> A Context Menu will appear >> Select Rename.

• Sheet2 is renamed to April.

You can also rename multiple sheets.

How to Activate a Worksheet

Left Clicking the Sheet Name

• Click any sheet tab to make it active.

Using the Keyboard and the Mouse

Ctrl + Left Click: You can Scroll to the last sheet

Right Click: You can See all sheets

To activate  January, right-click the right arrow.

• In  the Activate window >> Select January >> OK.

• You can activate January using a keyboard shortcut.

How to Move a Worksheet Tab in an Excel Workbook

• Left-click January and hold. Release it after February.

January will be displayed after February.

How to Change the Direction of an Excel Sheet From the Right to the Left or from the Left to the Right

The Excel sheet direction is from the left to the right by default.

1. Changing the Excel Sheet Direction from Left-to-Right to Right-to-Left

• Go to the File tab >> Options.
• In the Excel Options dialog box >> Select Advanced.
• In Display options for this worksheet  >> Check  Show sheet right-to-left  >> OK.

• You can also change the directions of the Excel sheet from the left to the right.

2. Changing the Excel Sheet Directions from Right-to-Left to Left-to-Right

• Press Alt + F + T  to open Excel Options >> Select  Advanced.
• Go to Display options for this worksheet>> Uncheck Show sheet right-to-left >> OK.

The direction was changed from the right to the left.

How to Delete a Worksheet in Excel

• Right-click the sheet tab >> Click  Delete.

• In the Microsoft Excel warning window >> Delete.

• Sheet2 was deleted.

You can also delete the multiple worksheets.

How to Copy a Worksheet in Excel

Within the Same Workbook

• Select January  >> Right-click the sheet tab >> Select Move or Copy .

• In the Move or Copy dialog box >> Select March.
• Check Create a copy >> Click OK.

The copied sheet (January (2)) is before March.

To Another Workbook

Open both workbooks. You will copy January (2) in the Excel Worksheets.xlsx workbook to the Scrolling in Excel.xlsm workbook.

• In the Move or Copy dialog box >> Select Scrolling in Excel.xlsm in To book.
• In Before sheet, select Smooth Scrolling.
• Check Create a copy >> Click OK

January (2) in the Excel Worksheets.xlsx workbook was copied to Scrolling in Excel.xlsm workbook before the Smooth Scrolling sheet.

How to Hide and Unhide Sheets in Excel

Hiding a Sheet in Excel

• Select February and right-click the sheet name >> In the Context Menu  >> Select Hide.

February is hidden from the sheet tab.

Unhiding Sheet in Excel

• Select any sheet >> Right-click the sheet name >> In the Context Menu >> Choose Unhide.

• In the Unhide dialog box >> Select February from the Unhide one or more sheets drop-down menu >> OK.

• Unhide the hidden sheet(s).

How to Group and Ungroup Worksheets in Excel

Grouping Adjacent and Nonadjacent Worksheets

Non-adjacent worksheet tabs: Click any sheet tab >> Press and hold Ctrl>> Select the sheets you want to group.

Adjacent worksheet tabs: Select any sheet tab >> Press and hold Shift, click the leftmost or rightmost sheet tab.

Ungrouping Adjacent and Nonadjacent Worksheets

• Right-click any grouped sheet tabs >> Choose  Ungroup Sheets in the Context Menu.

Note: You can also ungroup the worksheets by clicking any unselected sheet tab.

How to Protect an Excel Worksheet

• Right-click the sheet tab >> Choose Protect Sheet in the Context Menu.

• In the Protect Sheet window  >> Enter a password in Password to unprotect sheet.
• Choose your options in Allow all users of this worksheet to >> OK.

• In the Confirmed Password dialog box >> Reenter the password in Reenter password to protect >> OK.

March is protected with a password.

• To check whether the sheet is protected, select C11 and enter random data. A warning message will be displayed, confirming the sheet is protected.

Click here to enlarge image

How to Change a Worksheet Tab Color in Excel

By default, the color of a sheet tab is white. Observe the GIF below.

How to Compare Excel Sheets

1. Compare Excel Sheets with the View Tab

In the Same Workbook

• Go to the View tab >> Window  >> Click  New Window.

A new workbook window will open.

• By default, Excel displays two separate Excel windows horizontally.
• To display the windows vertically. Go to the View tab >> Window >> Click Arrange All.
• In the Arrange Windows dialog box  >> Check Vertical >> OK.

Both workbook windows are vertically displayed.

Click here to enlarge image

• To scroll both windows at a time, enable Synchronous Scrolling.
• Follow the steps in the GIF.

Click here to enlarge image

Different Workbooks

• Go to View tab >> Window >> Select View Side by Side.

The Synchronous Scrolling command will automatically be enabled for both workbooks.

Click here to enlarge image

• Select any cell and scroll to compare both workbooks.

2. Compare Excel Sheets with Conditional Formatting

• Select B5:E14 >> Go to the Home tab >> Styles >> Conditional Formatting >> Select New Rule.

• In the New Formatting Rule dialog box >> In Format values where this formula is true, select Use a formula to determine which cells to format.
• Enter the following formula:

• Click Format…

• In Format Cells, go to Fill and choose a color.
• Click OK.

• In New Formatting Rule, click OK.

• Different values between January and February are highlighted.

Click here to enlarge image

How to Zoom In and Out in Excel Worksheets

Observe the GIF.

How to Resize the Excel Window or View Excel in Full Screen

• You can use the Maximize and Minimize options.

• To enable Full Screen View press Ctrl + Shift + F1 keys.

Click here to enlarge image

• To resize the Excel sheet window, Drag the sheet window. Observe the GIF.

How to Limit Excel Sheet Size

1. Hide Unused Rows and Columns

Here, rows up to 14 and columns up to E in the June sheet will be hidden.

• Select any cell in column F. Here, F6 >> Press Ctrl + Shift + Right Arrow (→) to select the rest of the columns.
• Press Ctrl + 0 to hide columns.

Columns are hidden.

• Select the entire row 15 >> Press Ctrl + Shift + Right Arrow (↓) to select the rest of the columns.
• Press Ctrl + 9 to hide the selected rows.

Unused rows in June are hidden.

2. Using the Developer Properties Command to Limit Excel Sheet Size

• Go to the Developer tab >> Controls >> Properties command.
• In the Properties window >> Set scroll area in the ScrollArea. Here, $A$1:$E$20.
• Close the window (X).

Your Excel sheet is limited to the range $A$1:$E$20.

3. Using a VBA Code to Limit Sheet Size

• Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
• Select Insert  >> Module.

• In the VBA Module, enter the following code and run the macro.

Sub Limit_Sheet_Size()
Dim lss As Worksheet
Set lss = ActiveSheet
lss.ScrollArea = "$A$1: $E$20"
End Sub

How to Split Excel Sheets

• Select C9 >> Go to View >> Window >> Select Split.

Your sheet is split into four panes.

• Observe the GIF.

How to Count the Number of Sheets in an Excel Workbook

• In C5, enter the following formula and press Enter.

Download Practice Workbook

Excel Worksheets: Knowledge Hub

<< Go Back to Learn Excel