How do I send just one sheet in Excel?
Excel is great for creating spreadsheets with data and calculations, but sometimes it can be useful if you're on someone else's computer or you want to share your workbook without sending every cell individually. Thankfully there are some tools that let you easily create groups of cells within an Excel file, as well as separate specific sheets from the rest of the document so they act like their own standalone documents. Let's take a look at these features.
How do I group certain sheets in Excel?
The first step when working inside an Excel file is grouping all related information together. This allows other people who have access to the same spreadsheet to open up only what they need instead of having to scroll through tons of extra tabs. To get started, right-click any empty area of the ribbon menu (the buttons along the top edge of the screen) and choose Group Selection Pane " New Group.... You should see something similar to the screenshot below.
You will now see a new pane appear above the worksheets where you can drag and drop rows and columns between them. We recommend dragging everything over before selecting anything because otherwise you may end up accidentally moving things around. When you've finished organizing your content, click OK and then Close Grouping Area. The pane disappears automatically after a few moments.
Now whenever you go back to editing your worksheets, you won't see those pesky little panes anymore! If you ever decide you'd rather use them again, double-clicking anywhere in the pane brings it back, which also lets you add more rows and columns to it if necessary. For more options, check out our guide to using the Quick Access Toolbar in Office 2016.
How do I select just one sheet in Excel?
If you don't want to waste time managing groups of worksheets, another option is simply selecting and deselecting each tab separately. There are two ways to do this: by clicking the name of the active sheet in the upper left corner, or by holding down Ctrl while going across the tabs in order. In both cases, hold down Shift to highlight all subsequent sheets. Note that doing either method will not change the actual contents of the selected sheet(s). It merely highlights the current ones.
Here's an example where we start off with three worksheets containing different types of sales figures:
We now want to isolate the Sales Data section into its own worksheet, but since we haven't created one yet, it doesn't exist. So we highlight the Worksheet1 label and press F2 to jump straight to the second blank sheet in the list. Once here, we type CreateSalesData and hit Enter. Now we have a brand new sheet that contains nothing but the labels Worksheet1, Worksheet2, etc., plus whatever formulas we enter afterwards. Here's what it looks like:
To switch back to viewing all the existing sheets, just repeat the process until you find the desired sheet. Or you could keep pressing Alt+Tab until you reach the correct window.
How do I split an Excel worksheet into individual files?
Another way to make one large Excel file easier to manage is splitting it up into smaller chunks. After opening the file, head to File " Options " Trust Center " Advanced. Under the General heading, you'll see the box labeled Split Files Into Separate XLSX Sheets. Check this box and set the Number Of Pages Per Sheet field to however many pages you want. Then save changes.
Once you run the macro associated with this setting, you'll receive a message saying "This feature requires macros." Click Yes to continue. A dialog box appears asking whether you would like to convert your entire file or just convert a portion of it. Choose Convert Entire Workbook.
When prompted, browse to the location of your original Excel file. Name the resulting files according to your preferred naming convention. Don't forget to include the extension (.xlsx). Depending on your system settings, Windows might ask you to confirm renaming the converted files. Accept this prompt. Finally, click OK. Your conversion is complete.
Your final results should look like the image below. Notice how the header row stays consistent throughout. Also notice how much cleaner it makes reading, especially if you have lots of long names.
Can you split Excel sheet into multiple files?
Yes! All you need to do is follow the steps outlined earlier to break up your main Excel file into smaller sections. However, you must still copy and paste the headers once per section. Just be sure to always insert the headers AFTER copying them. Otherwise, you may wind up with duplicate numbers due to overwriting the old column titles.
For instance, say you had four columns titled 1st Quarter Results, 2nd Quarter Results, 3rd Quarter Results, and 4th Quarter Results. And suppose you wanted to divide that last quarter into two segments, called 1st & 2nd Quarters and 3rd & 4th Quarters. Instead of pasting the copied values directly onto the new sheet, place them into temporary dummy columns first. That way, you avoid confusion and ensure proper ordering later on.
Then use the following code to remove the dummy columns:
Dim wb As Workbook
Set wb = ActiveWorkbook
On Error Resume Next
With Application("Microsoft Excel") _
.Value = False
Range("A1", Cells(Rows.Count, "A"), Columns("A")).ClearContents
'Delete duplicated entries in Col B
r = 2
Do While Cells(r, 2) "" xlCellTypeSameAsPrevious
r = r + 1
'Delete duplicated entries in Col C
s = 2
Do While Cells(r, 3) "" xlCellTypeSameAsPrevious
s = s + 1
Cells(3, 1) = Empty
With Application("Microsoft Excel") _
.Value = True
Note that removing duplicates removes any formatting, including font properties and color schemes. If you prefer to maintain those details, try running the SortUnique command via the Developer tab instead. See Microsoft Support article 189439 -- How to sort items alphabetically, numerically, ascending or descending for additional help.
There are even times when you might want to combine several small Excel files into one larger file. For instance, maybe you were given permission to view a company database whose records contain dozens of columns. But you only need a handful of fields to draw conclusions about the business.
Instead of manually combining these spreadsheets, you can merge them all together using VBA. First, navigate to Tools " References in the Ribbon and tick Visual Basic for Applications Extensibility 5.0 Library. Next, write the following line of code to call MergeSheet function from the VBA editor:
Call MyFunction.MergeSheets ("D:\Test\MyFile1.xslm","D:\Test\MyFile2.xslm")
Replace D:\Test\MyFile1.xslm with the pathname pointing to the first Excel file, and D:\Test\MyFile2.xslm with the pathname pointing to the second Excel file. Run the project to test it out.
Do you have tips for navigating various parts of an Excel file efficiently? Are you curious about why we highlighted certain functions? Share your thoughts and questions below—we love hearing from you!
Do you want to know how to send only one sheet from your Microsoft Excel workbooks? Or maybe you have several sheets and would like to merge them into one for easier viewing? Here's how!
The ability to select multiple tabs in Excel is quite handy when working with large spreadsheets. However, it can be difficult if there are many columns and rows in each tab that need to get merged together. In addition, sometimes we may not be able to see all the information on screen because the pages keep scrolling down after merging the data. To solve these issues, here’s how you can easily send just one sheet of an Excel workbook via email. This guide shows you how to do both by using VBA code and without having to use any other third-party applications.
Can you send just one sheet of an Excel workbook?
Yes, you can send just one sheet (or even one range) from an Excel workbook by simply selecting the target sheet(s), right clicking on the selection, then choose Send to " Email Recipient…," followed by either E-Mail Message... or File Attachments…. For example, let us say that you wanted to send Sheet1 of an Excel Workbook named Book1 but instead, sent Sheet2 of another Excel Workbook called Book2. First, open Book1 and insert two new blank lines below the header row so that they appear at the end of the first column. Then copy the top half of Sheet1 which contains the headers. Paste the newly created empty rows onto Sheet3. Next, go back to Book1 and add some more values between those inserted empty lines. Now, press Ctrl + S to open Save As dialogue box. Name the file anything you wish and make sure that you don't include.xls extension. Click OK button to exit Save As dialogue box. Finally, click on Start program menu item, type cmd, and run command prompt window as administrator. Enter cd C:/Users/YourUserNameHere/Desktop where YourUserNameHere should actually reflect your actual username. Type dir book*.* /b |findstr excel |sort -r|more and hit enter key. The output will show the location of the Book1.exe application. Change directory to Book1 folder using CD command and double click on Book1.exe. Select Edit " Preferences. On the General Tab, check Show / hide protected system icons. Uncheck Display message icon in notification area when taskbar icon clicked. Under Advanced Options, uncheck Hide extensions for known file types option. Close Notepad and restart Explorer. You should now see Book1.exe under desktop shortcut icon. Right-click on Book1.exe, select Properties, and navigate to Compatibility section. Check Run this program as Administrator. Hit Apply, close properties window, and restart Explorer again. Open Book1.exe and drag the upper portion of Sheet2 onto Sheet3. Press F5 to launch Calc mode. Go to Tools " Macros " Refresh All Sheets. A dialog box will pop up asking whether you really want to refresh all sheets in the current spreadsheet. If yes, click Yes. After refreshing all sheets, view the result in Calc mode.
You should notice that the second sheet was successfully combined into the first sheet (Sheet 3).
Note: If you're running Windows 10 Anniversary Update version 1607 or later, please note that the above method no longer works. When trying to combine the sheets, you'll receive error message saying that the files cannot be opened due to unknown reason. Users who upgraded their systems to Windows 10 Version 1803 will likely face similar errors. Fortunately, Microsoft has developed its own solution for users experiencing such problems. Please refer to [Microsoft Support Article] How to Combine Multiple Spreadsheets Into One Using Office 2016 for instructions.
To summarize what we've covered so far, here are step-by-step instructions showing how to create a standalone Excel file containing selected ranges of cells within the same worksheet.
Can I send a single sheet of an Excel workbook?
No, unfortunately, this isn't possible unless someone else knows a trick. There is no way to embed only one particular sheet within an external file and attach it to an e-mail. It also doesn't seem practical since most people wouldn't print out that specific sheet alone anyway.
I'm still hoping there might be a workaround for this issue. So if you happen to find something useful, please share your thoughts through comments below.
Can I save an Excel sheet as a separate file?
This depends entirely upon the source of the original Excel file. Some sources allow saving an individual spreadsheet as a separate Excel file while others require you to convert them first. Let me explain further. Suppose you had five different worksheets saved as XLSM format and you were looking to convert them into XLSX format before sending them via e-mail. In that case, you'd better look for a converter tool. But what about converting an entire Excel workbook into XLSX format and then saving it as a separate file? Well, you could try doing that too. We recommend using OOXML Redo to perform such conversions. Here's how it works. First, download and install OOXML Redo. Launch the software. From the main interface, select Document Conversion Tool and click Browse to locate the Excel workbook (.xlsm/.xlsx) whose content you want to convert. Once done, you should be presented with options to customize conversion settings. Just select Convert & Compress Data and click Ok button. Depending upon size of converted file, it may take upwards of 30 minutes to complete the process. Upon completion, you should be greeted with a confirmation window stating Successfully Converted Documents.... View converted documents in OOXML viewer.
How do I save just one page of an Excel document?
We understand that it can be annoying if you need to manually scroll down to view a certain section in an Excel document. Thankfully, there exists a quick fix for this problem. Just follow our instructions below to achieve the desired results.
Open the Excel workbook you intend to extract one page from. Copy the header-footer region (i.e., the left side border of the page plus the bottom margin.) Hold down Shift+Ctrl keys, select HeaderFooter Region, and paste the copied contents to Page Setup dialogue box. Choose Insert Page field and set value to 1. Click ok. Repeat steps 2 and 3 until you finish extracting all pages from the Excel document.
Now, open Word and go to Home menu bar, select New Window, and click on More Files. Navigate to Desktop " My Computer " Local Disk (C:) " Program Files " Microsoft Office " Math" " XLOOKUP.EXE. Drag and drop extracted Pages individually into Word windows. Double-click on the last page thumbnail to automatically insert the table of contents for that particular chapter. Lastly, remove the header-footer regions with Alt + Backspace combination. Note that this procedure only applies to tables contained in Excel. If you plan to import graphs into Word, you must export them separately.
Enjoyed learning this interesting article? Do share it with friends!
Sometimes working on an Excel spreadsheets can be more convenient if we can send it via e-mail instead of downloading the file and opening it up in Excel. The problem is that when you have multiple tabs (or even one very long tab) in your document, sometimes sending it by mail becomes cumbersome. You might want to send only some specific cells from one particular worksheet, but not all of them. Or maybe you'd like to send only part of the excel workbook itself rather than the whole thing. In any case, here are three ways to accomplish those tasks without having to download and open your entire workbook into Excel first!
How do I send one Excel worksheet as a PDF?
If you're looking for a quick way to convert your current Excel worksheet into a nice clean PDF with no clutter at all, then check out the free utility called "Excel2PDF" [No Longer Available]. This application has been around for many years now, so there's plenty of support available online should you run into problems while using it. It comes highly recommended because it allows you to easily customize the output format (in terms of page size, number of pages per booklet, etc.) and also provides options to add notes, labels and comments within each individual page. And best of all -- it doesn't require Microsoft Office 2007+ installed on your computer, which means it will still work fine on XP machines too!
To use it, simply select everything on your desired worksheet(s) you wish to export, right click anywhere inside the selection area and choose Convert To -& Export As... A new window will pop up where you need to set the settings for the generated PDF. Make sure to enable both Print Area & Labels under Page Setup. Then go ahead and hit Save after making these changes. Your selected data will now be converted into a nice high quality PDF image. Now all you have left to do is attach it to an actual email message through Outlook or another compatible program.
Here's what our final results looked like once they were attached to an email:
Note: If you don't see the above menu option, try going back to previous versions of Windows before updating to Vista+. Also, make sure you've downloaded the latest version of the software. We tested the newest release 126.96.36.199 and found that it worked perfectly well on our test machine running WinXP Pro SP3. However, users who had reported issues previously said that old releases such as 2.5 did work correctly.
How do I send only certain sheets in Excel?
This method is particularly useful if you're trying to reduce the number of files sitting around on someone else's hard drive. For example, let say you have two identical copies of the same report saved onto different drives, and you'd like to consolidate their contents into one master copy. No worries though, this task isn't difficult to achieve. All you need is something called VBA code, which basically allows us to write scripts in order to automate things that usually take several clicks of the mouse to perform manually. So first off, create a macro to act as a filter on the content of your chosen worksheets. Here's how to do that step-by-step:
Load up the Visual Basic Editor, either by typing "visual basic editor" into your Start Menu search bar and choosing the Best Match, or by searching Google for tutorials on writing macros in general. Once you get started, you'll find it pretty easy. First, pick whatever range of cells you want to apply the filtering action to. Second, decide whether you would like to include rows, columns, or both. Third, name your module. Fourth, declare variables for your selections (i.e., ranges). Fifth, put in your logic behind selecting which cells belong to which groups. Sixth, save your script. All done! Now whenever you want to view only the subset of information contained in those selected cells, all you have to do is double click on the cell containing your command button, and voila -- your filtered list of values appears instantly!
For instance, if you wanted to generate a table consisting of only the names of employees with salary over $50K, you could input a command similar to the following into the Command Button field:
Dim wbkMain As Workbook
Set wbkMain = ActiveWorkbook 'Assign wbkMain variable to active workbook
'Select Range With Criteria
Selection.AutoFilter Field:=9, Criteria:="> 50K", VisibleDropDown False 'Criteria:="" hides column headers
.AutoFilter Field:=11, Operator:=xlOr, Criteria1:=""", VisibleDropDown:=True
As mentioned earlier, if you haven't already got familiarized yourself with the basics of creating and editing macros, please refer to other resources on the web for additional guidance.
Another interesting feature about this technique is its ability to allow user interaction. By adding a few lines of text between the Select Ranges section and the Apply AutoFilter portion, you can actually prompt the user with a friendly dialog box asking him or her to confirm whether he wants to proceed with the filtering process. When the user confirms his choice, the rest of the macro automatically runs based on his response.
The last advantage this approach offers over the aforementioned Excel2PDF tool is that you aren't limited to exporting the resulting data as a PDF. Instead, you can modify the Macro properties themselves to produce reports formatted however you prefer. For example, suppose your goal was to print out a nicely laid out PDF form filled out by hand. Well, you can certainly do that by modifying the template provided by Excel2PDF to suit your needs.
So in conclusion, if you're looking to extract subsets of data from a large Excel file, the methods discussed above offer great flexibility and efficiency. They won't leave much room for errors since all operations occur directly within the confines of the source file. But most importantly, they provide an excellent solution for people who often send Excel documents by e-mail due to space constraints.
We hope you enjoyed learning more about how to manipulate data in Excel and sharing it via e-mail. Remember, knowledge is power!