How to add a new sheet in spreadsheets. Renaming, inserting and deleting a sheet in Excel

Are the sheets at the bottom missing in Excel? When working in Excel, quite often a situation arises in which the buttons for changing sheets disappear. This is extremely unpleasant, since quickly switching between sheets is an important and convenient function. The reasons for the problem may vary. It can be solved using various methods.

First of all, you need to check whether the “Show sheet labels” checkbox is checked.

For Excel 2010:
1. Go to the “File” tab;
2. Select “Options”;
3. Then you need to go to the “Advanced” tab and find the “Show sheet shortcuts” entry there.

For Excel 2007:
1. Click the Office button in the upper left corner;
2. At the bottom of the menu that opens, select “Excel Options”;
3. Go to the “Advanced” tab and look for the “Show sheet shortcuts” entry.

For Excel 2003:
1. Click the “File” button;
2. Go to the “Options” tab, then select “View”;
3. We are looking for the “sheet shortcuts” function.

After making sure that the required item is checked, we follow another solution to the problem.
Sheet tabs may disappear if the window has been resized. This situation may arise if you opened several documents at the same time. You should check that the windows do not overlap.

Shortcuts may also disappear if the screen size is different from the screen size where the document was last opened.

If you are using Excel 2007, the buttons to switch between sheets may disappear after new macros and commands have been added.

Toggle buttons may be overlapped by the bottom scroll bar. You need to look carefully at the very bottom of the document and make sure that the scroll bar does not overlap the buttons for switching between sheets.

The desired sheet can also be hidden.
In order to display it, you must:

1. Right-click on any of the visible sheets;

Excel sheet shortcuts are located at the bottom left of the workbook area. The name of each worksheet appears on its associated label. However, any shortcut can be given a different name, up to a maximum of 31 characters.

This name can be used when addressing a sheet in Excel formulas.

Sheet Addressing

To use data located on certain sheets in a formula, you must put an exclamation point after the sheet name, then the cell address. The range of sheets in Excel is specified by a colon.

Examples of sheet addressing:

Sheet1!A1
Sheet12!G23
Quarter1!B4:E7
Sheet1!:Sheet3!A2

By default, Excel inserts three sheets into a newly created workbook.

Operations performed with worksheets are available by calling the context menu (Fig. 1). By right-clicking on a worksheet shortcut, you can select the desired operation from the menu.

Rice. 1. Context menu

The Excel worksheet name can be entered without using the context menu, for this:

1. Double-click the worksheet shortcut.

2. Enter the sheet name in the label area and click Enter.

To move between Excel sheets, click the shortcut for the sheet you want. The name of the current sheet will be highlighted in bold.

The sheet label scroll buttons can be used to move to other sheets. To move through the list of sheets, click the scroll buttons on the labels.

To change the number of displayed labels, drag the label field separator to the right (Fig. 2).

Rice. 2. Excel sheet shortcuts

Deleting, moving, or renaming an Excel sheet cannot be undone.

Adding a new Excel sheet

There are several ways to add new sheets to an Excel workbook.

Team Insert, Sheet used to add a new sheet to the current workbook. The new sheet will be inserted before the current sheet.

The procedure shown below can also be used to add a new Excel sheet.

1. Right-click the current worksheet shortcut to open the context menu.

2. Select Add. A dialog box will appear Insert.

3. Select Sheet from the dialog box and click OK.

Removing Excel sheets

To remove sheets from a workbook, you can use the command Edit, Delete Sheet or select the appropriate command from the context menu.

Copying and moving Excel sheets

When processing Excel spreadsheets, it is often necessary to move and copy sheets both within a workbook and to another workbook.

1. Select the sheet to move or copy.

2. Select Edit, Move or Copy Sheet or call the context menu and select Move or copy(Fig. 3).

A dialog box will appear Move or copy.

3. In the field To the book Select the workbook to which the sheets will be copied or moved. The book must be open to be selected

4. Select the sheet before which the sheet(s) will be inserted into the field In front of the sheet.

5. Enable the option Create a copy for copying. Otherwise the data will be moved.

6. Click OK after completion.

Rice. 3. Move or Copy dialog box

Just like Excel table cells, sheets can also be moved or copied using drag and drop. This method can be used to move or copy sheets within the same workbook, or to open books from one to another.

To move a sheet, drag the selected sheet's label to a new position. The pointer (triangle) marks the position where the sheets will be placed when the mouse button is released.

To copy Excel sheets, drag the selected sheets to a new position while holding down the key Ctrl. The appearance of a plus (+) symbol in the copy pointer indicates that the selected sheets will be copied.

Every Excel workbook contains at least one or more sheets. If you work with a lot of data, then using sheets will be very useful in terms of organization and ease of working with large amounts of data in your document.

In this tutorial you will learn how to rename a sheet and change the color of the sheet label, how to add, delete, copy, and move sheets. Moreover, you'll learn how to group and ungroup sheets, anchor columns and rows so that they are always visible when you scroll the sheet.

Working with sheets

When you open a new Excel workbook, it has three sheets by default. By default, they are called Sheet 1, Sheet 2 And Sheet 3. To organize the information in your workbook and make it easier to navigate, you can rename and even change the sheet label color. You can also add, delete, move and copy sheets.

To rename a sheet:

To create a new sheet:

Click on the Insert Sheet icon. A new sheet will appear.

You can change the settings for the default number of sheets that appear in a workbook. To access settings, go to the pop-up menu and click Options.

To delete a sheet:

Sheets can be deleted from a workbook even if they contain data.

1. Select the sheet you want to delete.
2. Right-click on the sheet shortcut. A context menu will appear.
3. Select Remove. The selected sheet will be removed from the workbook.

To copy a sheet:

To move a sheet:

To recolor a sheet label:

You can recolor the sheet labels to organize information and make it easier to navigate through the book.

Grouping and ungrouping sheets

You can work with each sheet separately or with several sheets at the same time. Sheets can be combined into a group. Any changes made to a sheet in a group will be applied to the rest of the sheets in that group.

To group sheets:

While the sheets are grouped, you can go to any of them and make changes that will appear on all sheets in the group. If you click on the tab of a sheet that is not included in a group, all your sheets will be ungrouped. You will be able to regroup them.

To ungroup all sheets:

1. Right-click on the sheet tabs. A context menu will appear.
2. Select Ungroup. The sheets will be ungrouped.

Freeze areas of the sheet

The ability to freeze certain rows or columns in a worksheet can be a very useful Excel feature. It's called pinning areas. When you freeze panes, you select rows or columns that will remain visible even as you scroll through the contents of the worksheet. This is especially useful when working with large sheets.

To freeze lines:

Columns are frozen in the same way as rows.

To freeze columns:

1. Select a column right from those that need to be secured. For example, if you want to freeze columns A and B, then you need to highlight column C.
2. Click on the View tab.
3. Select Freeze Regions.
4. A black line will appear to the right of the frozen columns. Scroll the sheet horizontally and you will see that the frozen columns remain in place.

To unpin areas:

1. Click on the View tab.
2. Click on the Freeze Areas command. A drop-down menu will appear.
3. Select Unlock Regions. The areas will be unlocked and the black line will disappear.

Practice!

1. Open an existing Excel 2010 workbook.
2. Create a new sheet.
3. Change the sheet name.
4. Remove the sheet.
5. Move the sheet.
6. Copy the sheet.
7. Try grouping and ungrouping multiple sheets.
8. Try freezing and unfreezing columns and rows.

Sheet labels for the current workbook in the Microsoft Office Excel spreadsheet editor are displayed by default at the bottom left edge of the window. If they are not there, there may be several reasons - from purely technical ones to those related to maintaining the increased confidentiality of the data contained in this book. Restoring the display of sheet labels is usually not difficult - it can take no more than five clicks.

You will need

• Spreadsheet editor Microsoft Office Excel 2007 or 2010.

Instructions

An Excel sheet allows you to create huge tables of thousands of rows and columns (in the 2016 version there are more than 16 thousand columns, more than a million rows). But often such volumes are not needed, but placement on several sheets is very much so. In this article you will learn how to solve the problem additions or removing sheets).

When loading the program, a workbook is created consisting of the number of sheets specified by default. For example, in version 2007 there are three sheets, and in 2016 there is one. You can add the missing sheet in a variety of ways:

• click on button next to the name of the existing sheet;

• take advantage right click and the team Insert;

• note the different order of sheets in this option (sheet2 can be moved behind sheet1 in the same way as you move icons on the desktop);

• use command menu Insert on the tab Home(a new sheet will appear in front of the one that already existed);

• again use the right mouse button, but select the command moving or copying, it is important to check the box in the dialog box creating a copy and select item move to end(then the new sheet will appear in order after the first);

• pay attention to the name of the sheet, if you want, you can always change it;

• press simultaneously SHIFT And F11 (a method for fans of "hot keys").

When performing this seemingly simple task in “deserved” versions of Excel, one difficulty is encountered: sometimes the names of the sheets are not displayed on the screen. In this case, you just need to expand the table window.

The fourth way to add sheets deserves a few more words. There are calculations that are repeated from month to month. The table headings are preserved, the formulas are the same, only the indicator values ​​change. In this case, it is more convenient to create a table on one sheet, then get the required number of copies of it on different sheets, rename and re-sort the sheets as necessary, and then delete indicator values ​​for periods that have not yet occurred.

You may also be faced with the opposite task: removing excess sheets. Again, there are several ways:

• right click on the sheet shortcut, open the context menu and select Delete;
• use command menu Delete on the tab Home.

If you need to speed up your work and insert or delete immediately some sheets, you must first select them (click on the desired label). If you select them using the key SHIFT, then the group includes everything between the first and last selected sheets, and if using CTRL, then only those that were clicked.

In this article, you learned some simple techniques, but this knowledge will help you make working in Excel efficient and comfortable.