How to open a second sheet in Excel. Rename, Insert and remove sheet in Excel

Excel sheet labels are located at the bottom of the workbook. The name of each working sheet appears on the appropriate label. However, any label can be given another name, no more than 31 characters long.

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

Leaf addressing

For use in the data formula located on certain sheets, it is necessary after the name of the sheet to put exclamation point, then the cell address. The range of sheets in Excel is set through the colon.

Examples of addressing sheets:

Sheet1! A1.
Sheet12! G23
Quarter1! B4: E7
List1!: Listz! A2

In the newly created book by default Excel inserts three sheets.

Operations performed with work sheets are available when calling the context menu (Fig. 1). By right-clicking on the working sheet shortcut, you can select the desired operation from the menu.

Fig. 1. Context menu

The name of the worker leaf Excel You can enter without using context menu, for this:

1. Click twice to the working sheet shortcut.

2. Enter the name of the sheet to the label area and click ENTER.

To move between Excel sheets, click the next sheet shortcut. The name of the current sheet is highlighted in bold.

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

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

Fig. 2. Excel sheets labels

Delete, move and rename the Excel sheet cannot be canceled.

Adding a new leaf Excel

There are several ways to add new sheets to the 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 on the current working sheet shortcut to open the context menu.

2. Select Add. A dialog box appears Insert.

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

Deleting Excel sheets

To remove sheets from the working book you can use the team Edit, remove sheet Or select the appropriate command from the context menu.

Copying and moving Excel sheets

In the process of processing electronic tables, Excel often needs to be moved and copy sheets both within the working book and to another book.

1. Select a 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 appears Move or copy.

3. In the field In a book Select a book to which sheets will be copied or moved. The book should be open to her can choose

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

5. Turn on the option Create a copy To copy. Otherwise, the data will be moved.

6. Tap OK after finishing.

Fig. 3. Move or copy dialog box

Like cells excel tables, Sheets can also be moved or copied by dragging. This method can be used to move or copy sheets within one working book or for Open Books from one to another.

To move the sheet, drag the label of the selected sheet 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 dedicated sheets to the new position when the key is pressed Ctrl. The appearance of a symbol plus (+) on the pointer when copying shows that the selected sheets will be copied.

Labels of sheets of the current book editor book editor Microsoft Office. Excel is displayed by default at the left lower edge of the window. If there are no, there may be several reasons - from purely technical, to compliance with the enhanced confidentiality of the data contained in this book. Return the display of sheet shortcuts, as a rule, is not difficult - it can take no more than five clicks with the mouse.

You will need

• MICROSOFT OFFICE Excel 2007 or 2010 tabular editor.

Instruction

Excel allows you to insert any number of new sheets in the book, delete unnecessary and renamed already created. Thanks to this, you can place any amount of information in the book, distributing it according to the appropriate sheets. In this lesson, we will learn to rename sheets in Excel, insert new, and also delete them.

By default, each Excel book contains at least one working sheet. Working with a large amount of data, you can create several sheets to systematize data and simplify the search for the desired information. You can also group sheets to quickly add information immediately to several sheets.

When creating a new Excel book, it will contain only one working sheet with heading Sheet1. You can rename this sheet so that it matches its contents. As an example, create a monthly diary for months.

In order to change the number of sheets open by default in the new Excel book, go to the Backstage view, click Parameters, then select the number of sheets you want to include in each new book.

Deleting a sheet from the working book cannot be canceled. This is one of the few irreversible actions in Excel. Think carefully before removing them.

If you need to limit the changes that are allowed to bring to the current sheet, you can protect it. To do this, right-click on the sheet shortcut and from the context menu, select item. Protect sheet.

When printing tables and other data, the Excel document is often case when the data goes beyond the borders of the sheet. It is especially unpleasant if the table does not fit horizontally. Indeed, in this case, the names of the strings will be on one part of the printed document, and separate columns on the other. Even more disappointment, if a little bit did not have enough space to completely place the table on the page. But exit from this position exists. Let's figure out how data on one sheet in various ways.

Print on one sheet

Before switching to solving the question of how to put the data on one sheet, you should decide whether to do it at all. It should be understood that most of those methods of which will be discussed below, suggest a decrease in the scale in order to fit them on one printed element. If the leaf limit is relatively small in size, it is quite acceptable. But if a significant amount of information does not fit, then an attempt to place all the data on one sheet can lead to the fact that they will be reduced so much that they will become unreadable. In this case, in this case, the best output will be a page on paper larger format, glue sheets or find another way out.

So the user must determine if it is worth trying to accommodate data or not. We will proceed to the description of specific ways.

Method 1: Change orientation

This method is one of the options described here, in which you do not have to resort to a reduction in the scale. But it is suitable only if the document has a small number of lines, or for the user is not so important that it fit into one page in length, and it will be enough that the data will be located on the sheet area in width.

There are also alternative option Leaf orientation shift.

1. Go to the "File" tab. Next, move to the "Print" section. In the central part of the window that opened the window is a print settings block. Click on the name "Book orientation". After that, a list with the ability to select another option. Choose the name "Loading Orientation".
2. As we see, in the preparation area, after the above actions, the sheet changed the orientation on the landscape and now all data is fully included in the print area of \u200b\u200bone element.

In addition, you can change the orientation through the parameter window.

1. Being in the "File" tab, in the "Print" section by clicking on the inscription "Page Settings", which is located at the bottom of the settings. In the window window, you can also get through other options, but we will talk in detail about the description of the method 4 in detail.
2. The parameter window starts. Go to his tab called "Page". In the "Orientation" settings block, we rearrange the switch from the "Book" position to the "landscape" position. Then click on the "OK" button at the bottom of the window.

The orientation of the document will be changed, and, therefore, the area of \u200b\u200bthe printed element is expanded.

Lesson: How to make a landscape sheet in exale

Method 2: Shift of the boundaries of the cells

Sometimes it happens that the sheet space is used inefficient. That is, in some columns there is an empty place. This increases the size of the page in the width, and therefore displays it beyond the limits of one printed sheet. In this case, it makes sense to reduce the size of the cells.

1. We establish the cursor on the coordinate panel on the border of the columns to the right of that column that you consider it possible to reduce. In this case, the cursor should turn into a cross with arrows directed into two sides. Clamp left button Mice and move the border to the left. This movement is continuing until the border reaches the data of the cell of the column, which is filled out more than others.
2. Such an operation is done with the rest of the columns. After that, it is significantly increasing the likelihood that all data of the tables will fit on one printed element, since the table itself becomes much more compact.

If necessary, such an operation can be done with lines.

The disadvantage of this method is that it is not always applicable, but only in cases where Excel's working sheet space was used inefficient. If the data is located as compact as possible, but still not placed on the printed element, then in such cases you need to use other options that we will talk about.

Method 3: Print Settings

It is possible to make all the data when printing on one item, you can also in the print settings by scaling. But in this case, it is necessary to consider that the data themselves will be reduced.

1. Go to the "File" tab. Next, move to the "Print" section.
2. Then again pay attention to the print settings block in the central part of the window. At the bottom there is a scaling settings field. By default, there must be the "Current" parameter. Click on the specified field. The list opens. Choose in it the position "Enter a sheet for one page".
3. After that, by reducing the scale, all data in the current document will be placed on one printed element, which can be observed in the preview window.

Also, if there is no mandatory need to reduce all rows on one sheet, you can select the "Enter columns per page" in the scaling parameters. In this case, the table data will be horizontally focused on one printed element, but in the vertical direction there will be no such restriction.

Method 4: Page Settings Window

Position the data on one printed element can also be using the window that is called "Page Settings".

As in the previous method, in the parameter window, you can make settings in which the data will be placed on the sheet only in the horizontal direction, and in the vertical limit will not be. For these purposes, it is required to rearrange the switch to the position "Post no more than on" in the "Page field" In width "set the value" 1 ", and the field" Page Height "leave empty.

Lesson: As a page in Excele

As you can see, there is a fairly large number of ways to accommodate all data for printing on one page. Moreover, the described options are essentially very different. The relevance of the use of each method should be dictated by concrete circumstances. For example, if you leave too much empty space in columns, then the most optimal option It will just move their borders. Also, if the problem is not to put the table on one printed element in length, but only in width, then it may make sense to think about changing the orientation to the landscape. If these options are not suitable, you can apply the methods associated with a decrease in scaling, but in this case the data size will also be reduced.

We are glad that you could help you solve the problem.

Ask your question in the comments, while playing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Will this article help you?

Now on the forum (guests: 867, users: 3, from them hidden: 1),

Today is celebrating Birthday (53), (50), (33), (26), (25), (39), (22)

Total registered users: 83372

Take part in the discussion: 31947

Total Top: 106188

Double click on the book to open it in Excel. This book should contain at least 2 sheets you want to combine.

Press + to create an empty sheet. This button is at the bottom of the book, to the right of the list of sheet.

Highlight the A1 cell. Just click on it to highlight.

Click on the Data tab at the top of the window, between the "Formula" and "Reviews" tabs.

Click on consolidation. This option is located on the toolbar in the "Work with Data" group. After that, the Consolidation window will appear on the screen.

Click on the amount in the "Function" drop-down menu. This is the first drop-down menu in the "Consolidation" window.

1. Press the arrow pointing up the arrow in the link field, in the cell right.

   So you reduce the Consolidation window and change its name to "Consolidation - Link".

   • In some Excel versions, this arrow gray and black. In other versions, it has the kind of a tiny rectangle in which the red arrow is located.

Select the data on the first sheet. To do this, click on the name of the sheet at the bottom of the screen, and then press the left mouse button and select the data you want to combine. The data will now be surrounded by a dotted line.

Click on the arrow in the "Consolidation - Data" window. You will again see the Consolidation window.

Click the Add to the right button to the "List of Ranges" field. Now you are ready to combine dedicated data with data on another sheet.

Press the up arrow in the Link field. As before, this will lead to a decrease in the "Consolidation" window and will change its name to "Consolidation - Link".

Highlight the data on the second sheet. Click on the name of the sheet at the bottom of the book, and then select the data you want to combine.

Click on the arrow in the "Consolidation - Link" window.

2. Click Add.

   In the "List of Ranges" field, selected data sets will appear.

   • If you want to merge other sheets, add data to the same method as with the first two sheets.

Tick \u200b\u200bnext to the option "Signatures top string"And" left column values. " This must be done in the lower left corner of the "Consolidation" window.

Click OK. Dedicated data will be combined and appear on a new sheet.

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

When loading the program, a working book is created, consisting of such a number of sheets, which is default. For example, in version 2007, three sheets, and in 2016 - one. Add missing leaf can be the most in many ways:

• click by button Next to the title of the existing sheet;

• take advantage right mouse button and team Insert;

• note the other order of the sheets in this embodiment (List2 can be moved behind the sheet1 just as you move the icons on the desktop);

• use the command menu Insert On the tab the main (The new sheet will rise before which was already);

• again use the right mouse button, but choose the command move or copyand it is important in the dialog box to check the box on create a copy And choose item move to the end (Then the new sheet will rise in order for the first);

• note the name of the sheet, if you want, it can always be changed;

• press at the same time Shift. and F.11 (Method for hotkeys' fans).

When performing this, it would seem simple, the tasks in the "deserved" versions of Excel meets one difficulty: sometimes the names of the sheets on the screen are not displayed. In this case, you just need to deploy the table window.

The fourth way to add sheets deserves several more words. There are calculations that are repeated from month to month. Table headers are saved, the formulas are the same, only values \u200b\u200bof indicators change. In this case, it is more convenient to create a table on one sheet, then get the desired amount of its copies on different sheets, sheets to rename and reorganize the necessary ways, and then remove the values \u200b\u200bof the indicators for the periods that have not arrived.

In front of you can also get the opposite task: remove extra sheets. There are several ways again:

• right mouse button On the sheet label to open the context menu and choose Delete;
• use the command menu Delete On the tab the main.

If you need to speed up and insert or delete immediately some Sheets, then you need to highlight them (click on the desired label). If you select them with the key Shift.then everything is in the group between the first and last dedicated sheets, and if using Ctrl, only those on which click is performed.

In this article you got acquainted with simple techniques, but these knowledge will help you to work in Excel rational and comfortable.