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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
- 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.
- 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.
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.
- 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.
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.
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.
Note: You can also exit the Full Screen View mode by pressing the Ctrl + Shift + F1 keys simultaneously.
You can also resize the Excel sheet window by dragging the sheet window. Take a look at the following image for clarification.
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.
Dim lss As Worksheet
Set lss = ActiveSheet
lss.ScrollArea = "$A$1: $E$20"
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.
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