Solution 5: Repair Blank Excel Workbook with Excel Repair Software. In case the “Excel 2016 opening blank” issue still exists, the Excel workbook you’re trying to open might be severely damaged. In this case, repair the Excel workbook using reliable and competent software Stellar Repair for Excel. This advanced application makes for the. Copy or Export data from one workbook to another. Good learning video for beginner in simple words. Top 25 Excel 2016 Tips and Tricks - Duration. VBA Macro to Copy Data from Another.
In some situations in Excel 2016, you need to move a particular worksheet or copy it from one workbook to another. To move or copy worksheets between workbooks, follow these steps:
![Data Data](/uploads/1/2/5/6/125642547/789186737.jpg)
- Open both the workbook with the worksheet(s) that you want to move or copy and the workbook that is to contain the moved or copied worksheet(s).Choose File→Open or press Ctrl+O to open both the workbooks.
- Select the workbook that contains the worksheet(s) that you want to move or copy.To select the workbook with the sheet(s) to move or copy, click its pop-up thumbnail on the Windows taskbar.
- Select the worksheet(s) that you want to move or copy.To select a single worksheet, click its sheet tab. To select a group of neighboring sheets, click the first tab and then hold down Shift while you click the last tab. To select various nonadjacent sheets, click the first tab and then hold down Ctrl while you click each of the other sheet tabs.
- Right-click its sheet tab and then click Move or Copy on its shortcut menu.Excel opens up the Move or Copy dialog box (similar to the one shown here) in which you indicate whether you want to move or copy the selected sheet(s) and where to move or copy them.Use the Move or Copy dialog box to move or copy from the current workbook into a different workbook.
- In the To Book drop-down list box, select the name of the workbook to which you want to copy or move the worksheets.If you want to move or copy the selected worksheet(s) to a new workbook rather than to an existing one that you have open, select the (new book) option that appears at the very top of the To Book drop-down list.
- In the Before Sheet list box, select the name of the sheet that the worksheet(s) you’re about to move or copy should precede. If you want the sheet(s) that you’re moving or copying to appear at the end of the workbook, choose the (Move to End) option.
- Select the Create a Copy check box to copy the selected worksheet(s) to the designated workbook (rather than move them).
- Click OK or press Enter to complete the move or copy operation.
If you prefer a more direct approach, you can move or copy sheets between open workbooks by dragging the sheet tabs from one workbook window to another. This method works with several sheets or a single sheet; just be sure that you select all the sheet tabs before you begin the drag-and-drop procedure.
To drag a worksheet from one workbook to another, you must open both workbooks. Click the Arrange All command button on the View tab or press Alt+WA and then select an arrangement (such as Horizontal or Vertical to put the workbook windows either on top of each other or side by side). Before you close the Arrange Windows dialog box, be sure that the Windows of Active Workbook check box is not selected; that is, does not contain a check mark.
After arranging the workbook windows, drag the worksheet tab from one workbook to another. If you want to copy rather than move the worksheet, hold down the Ctrl key while you drag the sheet icon(s). To locate the worksheet in the new workbook, position the downward-pointing triangle that moves with the sheet icon in front of the worksheet tab where you want to insert it; then release the mouse button or remove your finger or stylus from the touchscreen.
This drag-and-drop operation is one of those that you can’t reverse by using Excel’s Undo feature. This means that if you drop the sheet in the wrong workbook, you’ll have to go get the wayward sheet yourself and then drag and drop it into the place where it once belonged!
In the following figures, you can see how easy it is to move or copy a worksheet from one workbook to another using this drag-and-drop method.
Here, you see two workbook windows: Book1 new workbook (left pane) and the MGE – 2017 Projected Income workbook (right pane). These workbook windows are arranged with the View Side by Side command button on the View tab. To copy the Sprat Diet Ctr sheet from the MGE – 2017 Projected Income workbook to the new Book1 workbook, select the Sprat Diet Ctr sheet tab and drag the sheet icon to its new position before Sheet1 (no need to hold down the Ctrl key as Excel always copies between data between different files).
Copying the Sprat Diet Ctr worksheet to the Book1 workbook via drag and drop.
Now look at the next figure to see the workbooks after you release the mouse button. As you can see, Excel inserts the copy of the Sprat Diet Ctr worksheet into the Book1 workbook at the place indicated by the triangle that accompanies the sheet icon (before Sheet1 in this example).
Book1 workbook after moving a copy of the Sprat Diet Ctr before Sheet1.
Excel 2016 allows you to consolidate data from different worksheets into a single worksheet. Using the program’s Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets.
For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years. If you used a template to create each worksheet you’re consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets. However, even when the data entries are laid out differently in each spreadsheet, Excel can still consolidate them provided that you’ve used the same labels to describe the data entries in their respective worksheets.
Most of the time, you want to total the data that you’re consolidating from the various worksheets. By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or that use the same labels (when you consolidate by category). You can, however, have Excel use any of other following statistical functions when doing a consolidation: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, or VARP.
To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data. (If need be, insert a new sheet in the workbook by clicking the Insert Worksheet button.) To begin consolidating sheets in different workbooks, open a new workbook. If the sheets in the various workbooks are generated from a template, open the new workbook for the consolidated data from that template.
Before you begin the consolidation process on the new worksheet, you choose the cell or cell range in this worksheet where the consolidated data is to appear. (This range is called the destination area.) If you select a single cell, Excel expands the destination area to columns to the right and rows below as needed to accommodate the consolidated data. If you select a single row, the program expands the destination area down subsequent rows of the worksheet, if required to accommodate the data. If you select a single column, Excel expands the destination area across columns to the right, if required to accommodate the data. If, however, you select a multi-cell range as the destination area, the program does not expand the destination area and restricts the consolidated data just to the cell selection.
If you want Excel to use a particular range in the worksheet for all consolidations you perform in a worksheet, assign the range name Consolidate_Area to this cell range. Excel then consolidates data into this range whenever you use the Consolidate command.
When consolidating data, you can select data in sheets in workbooks that you’ve opened in Excel or in sheets in unopened workbooks stored on disk. The cells that you specify for consolidation are referred to as the source area, and the worksheets that contain the source areas are known as the source worksheets.
If the source worksheets are open in Excel, you can specify the references of the source areas by pointing to the cell references (even when the Consolidate dialog box is open, Excel will allow you to activate different worksheets and scroll through them as you select the cell references for the source area). If the source worksheets are not open in Excel, you must type in the cell references as external references, following the same guidelines you use when typing a linking formula with an external reference (except that you don’t type =). For example, to specify the data in range B4:R21 on Sheet1 in a workbook named CG Music – 2014 Sales.xlsx as a source area, you enter the following external reference:
Note that if you want to consolidate the same data range in all the worksheets that use a similar filename (for example, CG Music – 2012 Sales, CG Music – 2013 Sales, CG Music – 2014 Sales, and so on), you can use the asterisk (*) or the question mark (?) as wildcard characters to stand for missing characters as in
In this example, Excel consolidates the range A2:R21 in Sheet1 of all versions of the workbooks that use “CG – Music – 20” in the main file when this name is followed by another two characters (be they 12, 13, 14, 15, and so on).
When you consolidate data, Excel uses only the cells in the source areas that contain values. If the cells contain formulas, Excel uses their calculated values, but if the cells contain text, Excel ignores them and treats them as though they were blank (except in the case of category labels when you’re consolidating your data by category).