Excel Worksheets Basics – A Beginner’s Guide

Overview of Excel workbooks

This is an overview.

What Is an Excel Worksheet?

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.

Click on the plus (+) symbol

A new sheet will open.

Insert a new sheet

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.

Rename the sheet name

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.

Activate window

You can activate January using a keyboard shortcut.

Activate the January sheet

How to Move a Worksheet Tab in an Excel Workbook

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

Selection of the January sheet tab

January will be displayed after February.

Move a worksheet tab in an Excel workbook

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.

Excel sheet direction from left to right

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.

Check the Show sheet right-to-left checkbox

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

Excel sheet direction from right to left

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.

Uncheck the Show sheet right-to-left checkbox

The direction was changed from the right to the left.

Changing the direction from right to left

How to Delete a Worksheet in Excel

Right-click the sheet tab >> Click Delete.

Click on the Delete option

Press the Delete option from the Microsoft Excel window

Delete the Sheet2

How to Copy a Worksheet in Excel

Within the Same Workbook

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

Selection of the Move or Copy option

Move or Copy dialog box

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

Copied sheet January (2) before the sheet 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.

Choose the Move or Copy option

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

Copy January (2) sheet from one workbook to another workbook

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

Copying 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.

Selection of the Hide option

February is hidden from the sheet tab.

Hide the February sheet

Unhiding Sheet in Excel

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

Selection of the Unhide option

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

Unhide dialog box

Unhide the hidden sheet(s).

Unhide the hidden sheet

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.

Selection of the Protect Sheet option

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.

Entering password to protect sheet

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

March is protected with a password.

Reentering password to protect sheet

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

Microsoft Excel warning box

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

Selection of the New Window feature

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.

Arrange windows

Both workbook windows are vertically displayed.

Arrange workbook windows vertically to compare sheets

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

Select the View Side by Side feature

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

Opening workbooks on one screen

Click here to enlarge image

Select any cell and scroll to compare both workbooks.

2. Compare Excel Sheets with Conditional Formatting

Selection of the New Rule command

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:

=B5<>February!B5

Using conditional formatting formula

Choose color from the Fill tab

New Formatting Rule window

Different values between January and February are highlighted.

Highlighting the different values between January and February sheets

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.

Full Screen View mode in Excel

Click here to enlarge image

Note: You can also exit the Full Screen View mode by pressing Ctrl + Shift + F1 keys.

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.

Selection of the unused columns

Columns are hidden.

Hidden unused columns

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.

Selection of the unused rows

Unused rows in June are hidden.

Hidden unused rows

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).

Setting the limit of the scroll area to limit the sheet size

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.

Selection of the Module command

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

Copy and Paste VBA code in the Module

How to Split Excel Sheets

Selection of teh Split command

Your sheet is split into four panes.

Split Excel sheet

Observe the GIF.

How to Count the Number of Sheets in an Excel Workbook

In C5, enter the following formula and press Enter.

=SHEETS()

Using SHEETS function to count total sheets

Download Practice Workbook

Excel Worksheets: Knowledge Hub

Change Excel Sheet from Right to Left
Flip Excel Sheet from Left to Right
Grouping Sheets in Excel
Rename a Worksheet in Excel
Hide and Unhide Sheets in Excel
Organize Sheets in Excel