In this post, let's take a look at Excel 2007 as an example.adding new sheets to a workbook, copying an existing sheet, and creating sheets with specifiednames.
How to insert a new blank sheet into an Excel workbook?
To insert a new sheet, just select the "Insert ..." menu item, or press the button immediately following the last sheet of the workbook. Another way to add a new sheet to a workbook is by pressing the Shift + F11 keyboard shortcut. You can also configure the creation of books with an arbitrary number of sheets if you are not satisfied with the standard three sheets. You can do this by clicking the Office Button, then from the menu, select Excel Options, and under General, select the When Create Workbooks group. In the "Number of sheets" column, enter the desired value.
How to copy an Excel sheet or create an exact copy of it?
In order to make a copy of a sheet in Excel 2007, you need to select the sheet that you want to copy and right-click on it, select "Move / Copy ...".
How to change the name or rename a sheet of an Excel workbook?
In order to rename a sheet, you need to right-click on the tab of the desired sheet and select "Rename" from the context menu, in addition, you can simply double-click the left mouse button on the desired tab of the sheet and its name will become available for editing (except in those cases when structure and window protection is installed).
The steps described above refer to standard Excel tools and they are quite enough when working with workbooks that have a small number of sheets. For mass adding worksheets, inserting a large number of copies, as well as creating sheets with given names, it is advisable to use VBA tools.
How do I re-insert blank sheets of paper into a book?
How do I create multiple copies of a sheet? How to add sheets with names from cells to a workbook
When working with files containing many sheets, or if you need to periodically replicate, make many copies of a given sheet, for example, when creating receipts in Excel, an alternative to standard, manual addition or copying of sheets can be macro (add-on)... The aforementioned macro allows you to automatically insert the required number of new sheets, create a specified number of copies of the selected sheet, and also makes it possible to create new sheets and assign them names based on cell values.
In order to use this tool, you must install the add-on on your computer, call the add-in dialog box and set the initial data for the program to work in it.
video on working with the add-in
add-on (macro) for adding new sheets and copying existing ones
You can choose one of the available options:
1. Insert new sheets in the specified number
The user sets the required number of sheets, selects the option of inserting sheets and starts the program, the result of which is a workbook with a given number of blank sheets.
2. Copy the active sheet a specified number of times
The copied sheet is selected, by analogy with the previous paragraph, the desired number of copies is set, as a result of the program's action, the specified number of copies of the active sheet appears in the book.
3. Assign names from cells to new sheets
Instead of creating new sheets with the standard names "Sheet1", "Sheet2", "Sheet3" and so on, and then renaming them, spending a lot of time on this operation, you can pre-form a list of names in the cells of a sheet of the workbook, and then in in the add-in dialog box, select the range with the desired names and run the program. Before creating sheets with names from cell values, these values are checked against the requirements for sheet names that.
In Excel, you often have to print the results of analytical work. It is good when this is accompanied by several printed sheets, and if there are a lot of them, then it becomes necessary to learn how to number the pages in Excel so that, after printing, they can be folded without error in the correct order.
For printing, data is numbered according to the specified format
Excel is a spreadsheet editor, therefore, a solid table is placed and printed on the entire space, so it is so difficult to figure out where the table continues and where its previous part is. It is for this reason that many are actively looking for an answer to the question of how to make pagination in Excel.
Solid numbering
It is not difficult to set the numbers of all printed sheets. Initially, of course, you should open the file in which the page order will be carried out. At the top of the toolbar, you need to find "View", by default the application is accompanied by the usual view, in the submenu you must select "Markup".
After that, on the screen, you can observe how the document is divided by page. If the user is satisfied with everything, there is no need to edit something, you need to go to the "Insert" section and select "Headers and Footers" there.
By activating this item, a context menu appears on the screen, allowing you to work with headers and footers. In the proposed list, you need to find the phrase "Page number" by clicking on it.
If all the actions were performed correctly, then text such as "& [Page]" will appear in Excel. This will indicate that the numbers have been successfully added to the spreadsheet editor. It should be noted that by clicking on this text, it will immediately change and show the page order. If there is no information in this place, then it will not be possible to see the numbering, since only the filled page spaces can be numbered.
After completing in practice all the steps described in the recommendations on how to number pages in Excel, it becomes clear that no problems arise when it is necessary to put down numbers.
How to number from a specific place
Sometimes it becomes necessary to carry out numbering not from the first sheet, but most often from the second. This is typical when the user performs a term paper or thesis, in which the title page is supposed to be placed at the very beginning. In order to accurately make the numbering under such conditions, it is useful to study the advice of experienced users on how to put down page numbers in Excel, starting from the second sheet.
Numbering from a specific place
If you need to remove the numbering from the very beginning, you need to find "Special header on the first page" in the header and footer constructor, and then check the box next to it. Now this is the only place where the header / footer will be subject to change, the rest will remain intact. It will now be quite easy to delete an ordinal pointer manually by clicking on it and pressing Delete.
In practice, everything becomes clear, and even an inexperienced user will be able to tell the same inexperienced colleagues how to remove the document numbering in Excel.
However, in some cases, a specific need arises when the ordinal pointer must be set not even from the second, but from the seventh or some other position.
In order for the numbers to be printed not immediately, but starting from a certain sheet, you need to go to the "Page settings", and then go to the "Page" tab. At the bottom, it is important to find the text "Number of the first page", by default it is set to "auto", which corresponds to the fact that the page order will be carried out from the first sheet. If it is necessary to make changes, then instead of the word "auto" a numerical indicator is set, indicating from which printed sheet the ordinal indicator will be affixed.
By default, sheet shortcuts for the current workbook in the Microsoft Office Excel spreadsheet editor are displayed 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 the increased confidentiality of the data contained in this book. Reverting the display of sheet labels is usually not difficult - it can take no more than five clicks.
You will need
- Tabular editor Microsoft Office Excel 2007 or 2010.
Instructions
Excel sheet shortcuts are located at the bottom left in the workbook area. The name of each worksheet appears on the corresponding label. However, any shortcut can be given a different name, up to 31 characters long.
This name can be used when addressing a sheet in Excel formulas.
Sheet addressing
To use the data located on certain sheets in the formula, it is necessary to put an exclamation mark after the sheet name, then the cell address. The range of sheets in Excel is separated by a colon.
Examples of sheet addressing:
Sheet1! A1
Sheet12! G23
Quarter1! B4: E7
Sheet1!: SheetZ! A2
Excel inserts three sheets into the newly created workbook by default.
Operations performed with worksheets are available when 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 name of the Excel worksheet can be entered without using the context menu, for this:
1. Double click on the worksheet shortcut.
2. Enter a sheet name in the shortcut area and click Enter.
To move between Excel sheets, click the shortcut for the sheet you want. The name of the current sheet is highlighted in bold.
The scrolling buttons on the sheet tabs can be used to navigate to other sheets. To move through the list of sheets, click the scroll buttons on the labels.
To change the number of labels to display, drag the label field separator to the right (Figure 2).
Rice. 2. Excel sheet shortcuts
You cannot undo operations to delete, move, and rename an Excel sheet.
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 on the shortcut of the current worksheet to open the context menu.
2. Select Add. A dialog box will appear Insert.
3. Select Sheet from the dialog and click OK.
Deleting 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.
Copy and move Excel sheets
In the process of processing Excel spreadsheets, it is often necessary to move and copy sheets both within a workbook and into 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 Into the book select the book 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 in the field In front of the sheet.
5. Enable the option Create a copy to copy. Otherwise, the data will be moved.
6. Click OK after finishing.
Rice. 3. Dialog box Move or copy
Like cells in an Excel spreadsheet, 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 the sheet, drag the tab 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 selected sheets to a new position while holding down the key Ctrl. The appearance of the plus (+) symbol on the index during copying indicates that the selected sheets will be copied.
The Excel sheet is the work area below the toolbar. The sheet consists of many cells arranged among themselves in relation to rows and columns.
Its appearance resembles a large table. Each new Excel workbook has 3 sheets by default.
For the user, a sheet is a file with a document that is attached to a binder (Excel workbook). Sheets can and should be managed. They can be moved to other workbooks. This operation saves a lot of time than just copying and transferring the contents of the sheet itself.
Excel sheets as a data environment
In a standard workbook, each sheet is a large spreadsheet that is filled with data and formulas. How to add a sheet to Excel? As needed, you can insert additional sheets (SHIFT + F11) or delete unused ones. You can make a new sheet in Excel by clicking on the last tab of sheets, as shown in the figure:
The book can contain from 1 to 255 sheets.
How to copy a sheet in Excel? For the quick implementation of this task, it is necessary:
- Move the mouse cursor over the sheet tab (shortcut) that you want to copy.
- Make a click, with the left button of the bear, while holding down the + CTRL key, move the cursor to the place of the sheet tabs where you want to paste the copy.
If there are a lot of sheets or it is necessary to copy / move a sheet to a new book, then we do the following:
Exercises with sheet elements
Task 1: Click on each element shown in the picture above and try to remember their location, name.
Task 2: Click on the tabs at the bottom (Sheet2, Sheet3 and Sheet1) alternately. When you click, the tab of each sheet becomes active, and its tab is highlighted.
Note: each book can contain many sheets (up to 255 pcs.), Just like a binder contains files.
We will consider various operations with sheets in the following lessons.
Working with sheet cells
The most important element of a sheet is the cell. Each cell has its own serial number relative to the lines and the Latin letter relative to the columns. Thus, each cell is assigned its own address (like cells in a chessboard). That is, the first cell (in the upper left corner) has the address A1, and below it is the cell with the address A2. On the right side in the cell is the address B1, etc.
The number of rows in each sheet of Excel 2010 is slightly more than one million, or rather 1,048,578 pieces. The number of columns is much less - 16 384 pcs.
Note: Pay attention! After 26 Latin letters, the name of the columns no longer consists of one, but of 2 or more letters in a logical, alphabetical order. Therefore, the address of the last cell in the very bottom right corner of the worksheet is XFD1048578.
Each cell can be written:
- text;
- number;
- date and time;
- formulas;
- logical expressions.
Each cell can be formatted and annotated. All data and formatting styles of cells can be changed. It is also worth noting that the formatting of cell borders is a key basis for the design of the appearance of tables (for example, for printing forms, etc.).
