Excel Worksheets Basics – A Beginner’s Guide

Excel worksheets are like digital pages where you can type, calculate, and analyze data. They are divided into rows and columns to help you keep things neat and organized.

In this article, you will learn everything about Excel worksheets.

After reading this blog post, you will learn how to:

– Change the direction of an Excel sheet from right to left or left to right.
– Delete a worksheet.
– Copy a worksheet.
– Hide and unhide a sheet.
– Group and ungroup worksheets.
– Protect cells in an Excel worksheet.
– Change the sheet tab color.
– Compare Excel sheets using the View tab and Conditional Formatting.
– Refresh an Excel worksheet.
– Zoom in and out.
– Resize the Excel window or view Excel in full screen.
– Limit the size of an Excel sheet.

Lastly, we will discuss how to split Excel sheets and count total sheets in a workbook.

Note: While writing this article, we applied all procedures using Excel for Microsoft 365.

⏷What Is an Excel Worksheet
⏷Create an Excel Worksheet
⏷Add New Worksheet
⏷Rename a Worksheet
⏷Activate a Worksheet
⏷Move a Worksheet Tab
⏷Change Excel Sheet Directions
⏷Delete a Worksheet
⏷Copy a Worksheet
⏷Hide and Unhide Sheet
⏷Group and Ungroup Worksheets
⏷Protect Cells in an Excel Worksheet
⏷Change Sheet Tab Color
⏷Compare Excel Sheets
⏷Zoom In and Out
⏷Resize Excel Window
⏷Limit Excel Sheet Size
⏷Split Excel Sheet
⏷Count Total Sheets in an Excel Workbook

What Is an Excel Worksheet?

An Excel worksheet is a grid-based document used for organizing and analyzing numerical data. It is the core component of Microsoft Excel, which is a spreadsheet software application.

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 within an Excel worksheet.

How to Create an Excel Worksheet?

To create an Excel worksheet, we follow the steps below.

• Open Microsoft Excel and you can see the variety of worksheets >> Select the Blank workbook option to create a blank Excel worksheet.

• As a result, you can create a blank Excel worksheet named Sheet1 as seen in the following screenshot.

How to Add New Worksheets in Your Workbook?

To insert a new worksheet in an Excel workbook-

• Press the plus (+) symbol at the bottom to insert a new worksheet.

• Hence, you can insert or open a new sheet.

Have a look at the below GIF file for a better understanding.

Note: You can insert a new worksheet instantly by using the keyboard shortcut Shift + F11 keys simultaneously.

How to Rename a Worksheet?

You can rename a worksheet in Excel by using the Context Menu. From our dataset, we’ll rename the sheet named Sheet2 to April.

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

• Finally, you can rename the sheet named Sheet2 to April.

You can also rename multiple sheets in the same way.

How to Activate a Worksheet?

You can make a worksheet active by left-clicking on the corresponding Sheet Name or using a keyboard and mouse.

Left Clicking on Sheet Name

• Click on any sheet tab to make it active. For a better understanding, take a look at the image below.

Using Keyboard and Mouse

You can also do that by using a keyboard and mouse.

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

Right Click: You can See all sheets

You want to activate the sheet named January. To do that, right-click on the right arrow.

• Activate window pops up >> Select January >> OK.

• Finally, you can activate the January sheet by using the keyboard shortcut.

How to Move a Worksheet Tab in an Excel Workbook?

We want to move the January sheet after the February sheet. To move a worksheet tab, follow the below steps.

• Left-click on the January sheet tab and hold it. After that, release it after the February sheet tab.

• As a result, you can move the January sheet after the February sheet like the following screenshot.

For a better understanding, have a look at the following GIF image.

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

The Excel sheet direction is from left to right by default. It shows the row numbers and sheet tabs on the left. The column numbers also start from the left and move to the right.

1. Change Excel Sheet Direction from Left-to-Right to Right-to-Left

You can change the directions of sheet tabs, row numbers, and column numbers from left-to-right to right-to-left. Here the row numbers and sheet tabs are on the right. The column numbers also start from the right.

• Go to File tab >> Options.
• Excel Options dialog box pops up >> Select the Advanced tab.
• Hence, go to the Display options for this worksheet section >> Check the Show sheet right-to-left checkbox >> OK.

• After that, you can change the directions of the Excel sheet from left to right.

2. Change Excel Sheet Directions from Right-to-Left to Left-to-Right

Now, follow the methods below to back the default orientation from right-to-left to left-to-right.

• Press the Alt + F + T keys one after another to open the Excel Options >> Select the Advanced tab.
• After that, go to the Display options for this worksheet section >> Uncheck the Show sheet right-to-left checkbox >> OK.

• As a result, you can change the direction from right to left.

How to Delete a Worksheet in Excel?

To delete a worksheet, e.g., Sheet2, follow the steps below.

• Right-click on the sheet tab >> Click on the Delete option from the Context Menu.

• Microsoft Excel warning box pops up >> Delete.

• Finally, you can delete the worksheet named Sheet2.

You can also delete the multiple worksheets in the same way. The procedures are given in the below image.

Notes: If you delete a blank sheet, the Microsoft Excel warning box won’t pop up.

How to Copy a Worksheet in Excel?

We will show you how to copy a sheet by pressing the right-click on the Mouse in Excel. You can use this method to copy a sheet within the same workbook and to another one.

Within Same Workbook

• Select the January sheet >> Right-click the Mouse on that sheet tab >> Select Move or Copy option.

• After that, the Move or Copy dialog box will pop up >> Select March from the Before sheet group.
• Check the Create a copy option >> Click OK.

• After that, you will find the copied sheet January (2) before the sheet March.

To Another Workbook

To copy a sheet from one workbook to another by right-clicking the mouse, you must open both workbooks. From our dataset, we’ll copy the January (2) sheet from the Excel Worksheets.xlsx workbook to Scrolling in Excel.xlsm workbook.

• Hence, the Move or Copy dialog box will appear >> Select Scrolling in Excel.xlsm from the To book drop-down.
• Select Smooth Scrolling from the Before sheet group.
• Check the Create a copy option >> Click OK

• Hence, you can copy the January (2) sheet from the Excel Worksheets.xlsx workbook to Scrolling in Excel.xlsm workbook before the Smooth Scrolling sheet.

How to Hide and Unhide Sheets in Excel?

The simplest way to hide and unhide worksheets from workbook is to use right-click on the worksheet. We just need to follow some steps.

Hiding Sheet in Excel

• Select the February sheet tab and right-click on the sheet name >> A Context Menu will appear >> Select the Hide option.

• As a result, the February sheet is hidden from the sheet tab.

Unhiding Sheet in Excel

• Select any sheet from the sheet tab >> Right-click on the sheet name >> A Context Menu will pop up >> Choose the Unhide option.

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

• Finally, you can unhide the hidden sheet(s).

How to Group and Ungroup Worksheets in Excel?

You can group and ungroup the adjacent and nonadjacent worksheet tabs. If you change any data on any grouped sheet tab, it will automatically change the data on every sheet tab.

Grouping Adjacent and Nonadjacent Worksheets

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

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

Follow the GIF image below.

Ungrouping Adjacent and Nonadjacent Worksheets

Right-click any grouped sheet tabs >> Choose the Ungroup Sheets option from the Context Menu.

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

How to Protect Excel Worksheet?

In this section, we’ll learn how to protect cells in an Excel worksheet. From our dataset, we’ll protect the sheet named March.

Follow these steps:

• Press right-click on the sheet tab >> Choose the Protect Sheet option from the Context Menu.

• Protect Sheet window pops up >> Enter any password in the Password to unprotect sheet box.
• Choose your options from the Allow all users of this worksheet to group >> OK.

• Another dialog box named Confirmed Password appears >> Reenter the password in the Reenter password to protect box >> OK.
• After that, you can protect the March sheet with a password.

• Now, let’s check whether the sheet is protected or not. To do that, select cell C11 and type anything. Instantly, a warning message pops up to confirm that our sheet is protected.

Click here to enlarge image

How to Change Worksheet Tab Color in Excel?

Now, we’ll change the sheet tab color in Excel. By default, the color of a sheet tab is white. You can change it to any color you want. To change a sheet tab color, follow the GIF image below.

How to Compare Excel Sheets?

In this example, we’ll learn how to compare Excel sheets in the same and different workbooks with the View tab and Conditional Formatting. You can compare data in two or more worksheets to ensure accuracy, identify discrepancies, and maintain consistency for reliable data analysis, and decision-making in Excel.

1. Compare Excel Sheets with View Tab

You can use the View Side by Side feature from the View tab to compare Excel sheets in the same and different workbooks.

Same Workbook

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

• As a result, a new workbook window will open with a similar name.
• By default, Excel displays two separate Excel windows horizontally.
• Now we’ll arrange the windows vertically. To do that, again, go to the View tab >> Window group >> Click the Arrange All feature.
• Arrange Windows dialog box pops up >> Mark the Vertical option from the Arrange group >> OK.

• As a result, you can arrange both workbook windows vertically.
• Now, you can compare both workbook windows.

Click here to enlarge image

• If you want to scroll both windows at a time, you need to enable the Synchronous Scrolling command.
• To do that, follow the below GIF image.

Click here to enlarge image

Different Workbooks

You can compare different workbooks at a time using the View Side by Side feature and Synchronous Scrolling command of Excel. These workbooks must be opened for comparison.

• Go to View tab >> Window group >> Select the View Side by Side feature.

As a result, the Synchronous Scrolling command will be enabled automatically for both workbooks.

Click here to enlarge image

• Now, select any cell from any workbook and scroll the mouse wheel.
• You can also compare both workbooks, as these workbooks are shown vertically.

2. Compare Excel Sheets with Conditional Formatting

You can compare Excel sheets and highlight the different values with conditional formatting. From our dataset, we’ll compare between the January and February sheets.

Follow these steps:

• Select data range B5:E14 >> Go to Home tab >> Styles group >> Conditional Formatting drop-down >> Select New Rule command.

• A New Formatting Rule dialog box pops up >> Select Use a formula to determine which cells to format. Under Format values where this formula is true:, type the following formula:

• Click on Format…

• A dialog box named Format Cells will open. Under the Fill tab, choose any appropriate color and click on OK.

• Again, inside the New Formatting Rule click on OK.

• As a result, you can highlight the different values between January and February sheets.

Click here to enlarge image

How to Zoom In and Out in Excel Worksheets?

You can Zoom In or Zoom Out of an Excel worksheet to better understand the data table. Take a look at the image below to see how zooming works.

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

In this section, we’ll learn how to resize an Excel worksheet. You can do it by using the Maximize, and Minimize options (You can find these two options in the right-top corner of your Excel worksheet window), dragging the sheet, and Keyboard shortcuts.

You can enable the Full Screen View mode in Excel by using the keyboard shortcut. This mode is really helpful when you use a large dataset. Using this mode, you can see the full screen with no title bar in Excel. To do that, simply press Ctrl + Shift + F1 keys simultaneously.

Click here to enlarge image

You can also resize the Excel sheet window by dragging the sheet window. Take a look at the following image for clarification.

How to Limit Excel Sheet Size?

Limiting Excel sheet size means preventing others from viewing additional rows and columns. It helps you to focus on a certain part for better focus.

There are 3 ways we can limit Excel sheet size:

– Hide Rows and Columns
– Using the Developer Properties command
– Using a VBA Code

1. Hide Unused Rows and Columns

Hiding unused rows and columns, you can limit an Excel sheet size. We will limit the rows up to row 14 and the columns up to column E in the June sheet from our dataset.

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

• Hence, you can hide columns in the June sheet.

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

• After that, the unused rows in the June sheet can be hidden.

Using the ways above, you can limit an Excel sheet size.

2. Using Developer Properties Command to Limit Excel Sheet Size

• Go to Developer tab >> Controls >> Properties command.
• After that, the Properties window will appear >> Set scroll area in the ScrollArea The range in my case is $A$1:$E$20.
• Finally, click the Close (X) button to close the Properties window.

• Your Excel sheet can be limited to the range of cells $A$1 to $E$20.

3. Using a VBA Code to Limit Sheet Size

You can use a VBA code to limit every active sheet in Excel. Active sheet means the sheet you are working on currently.

Follow these steps:

• Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
• After that, select Insert tab >> Module.

• Then a VBA Module will be launched. In that 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?

In this section, we’ll learn how to split screen or an Excel sheet within the same sheet. You can split your Excel sheet into panes to observe or view simultaneously multiple distant sections of your spreadsheet.

Follow these steps to split an Excel sheet:

• Select cell C9 >> Go to View tab >> Window group >> Select the Split command.

• As a result, you can split your sheet into four panes like the following screenshot.

Have a look at the below image for a better understanding.

How to Count the Number of Sheets in an Excel Workbook?

You can use the SHEETS function to count the total number of sheets in a workbook in Excel.

• In cell C5, write down the following formula and press Enter to count the total sheets.

Download Practice Workbook

In the discussion above, you have learned all of the functions of Excel worksheets. You have also learned how to create or insert, rename, make a worksheet active, copy a workbook, hide or unhide sheets, group or ungroup sheets, change sheet tab color, refresh a worksheet, and zoom in or out the Excel worksheets. You also know now how to compare Excel sheets and resize an Excel window. Lastly, we have discussed the limiting Excel sheet size.

Excel Worksheets: Knowledge Hub

<< Go Back to Learn Excel