Admittedly, it sounds like a basic topic: Saving Excel files. But besides saving complete workbooks, there are a lot of potential improvements and methods for saving time. How do you for example save a workbook as a PDF file? Or how do you export single worksheets? Everybody probably has their own steps, but oftentimes there are faster and more convenient ways. In this article we explore the best and fastest ways of saving your workbooks or separate sheets as PDF files or Excel files to your hard drive.
This article is part of a series: Before we’ve talked about how to attach a workbook to a new e-mail the fastest way. Now we take a look at how to save files to the hard drive in the same manner.
- Definition of saving types
- How to save which file type the best way
- Free Download: Get all these tips summarized in one printout
- Taking it one step further: Save both PDF and Excel files at the same time with just one click
Definition of saving types
Before we start, we have to define the possible methods. We differentiate between automatic, manual and add-in as follows.
- Automatic: When we refer to saving a file automatically, we actually mean ‘one-click-solutions’. For example by clicking one button and you only have to select the folder and file name.
- Manual: In terms of saving files, manually means that you have to conduct several steps in order to eventually save a file to the hard drive. For instance, you have to copy worksheets, cut the workbook links and save them in the PDF or Excel format.
- Add-in: For all the saving types, there is an add-in which offers a ‘one-click-option’. In our case, we refer to our own Excel add-in called ‘Professor Excel Tools‘. So if all the other methods don’t work, you can still try the corresponding button of this add-in.
For comfortably saving files, we recommend to add the button “Publish as PDF or XPS” (letter A in the picture on the right hand side) to your Quick Access Toolbar. Please follow these steps (the numbers are corresponding to the picture on the right hand side):
- Right-click on any Ribbon or button.
- Select “Customize Quick Access Toolbar”.
- In the opening window, set “Choose commands from” to “All Commands”.
- Search in the alphabetical list for “Send to Mail Recipient”.
- Click on “Add”.
- Change the order with the up and down arrows on the right hand side if necessary.
- Click “OK” to save the changes.
How to save which file type the best way
Let’s examine four different situations: We want to save a PDF or an Excel file to a new e-mail. In both cases we want to save either the complete workbook or selected sheets only. So that makes 2 x 2 situations. For each of these situations, there are different ways most comfortable.
File type: PDF – Scope: Single/ selected sheets
- Automatic: You can save single worksheets or several selected worksheets with one click. Therefore, add the button “Publish as PDF or XPS” (letter A in the picture above) to your Quick Access Toolbar. Just click the button and select the folder and file name.
- Add-in: ‘Professor Excel Tools‘ provides a button for saving PDF files or worksheets with just one click.
File type: PDF – Scope: Complete workbook
- Select all sheets. Therefore click on the leftmost worksheet name. Next, jump to the right hand side of the sheet tabs (shortcut: press Ctrl and click on the small arrow in the left bottom corner). Press Shift and click on the rightmost sheet name.
- Now press the button “Publish as PDF or XPS” (letter A in the picture above). Excel then asks you to pick a folder and file name.
- Add-in: ‘Professor Excel Tools‘ provides a button for saving complete workbooks as PDF files with just one click.
File type: XLSX – Scope: Single/ selected sheets
- Automatic: There is no direct/ automatic way.
- Select all worksheets you’d like to save separately.
- Copy the selected worksheets to a new Excel workbook by right clicking on one of the sheet names. Next, click “Move or copy…” and select “(new book)” in the upper drop down box. Set the tick at “Create a copy” and confirm with OK.
- Cut the data links. Please refer to this article for more information about how to break all links.
- Save the workbook under a new name. Therefore press Ctrl + s (opens the Save dialogue) or F12 (opens the ‘Save as…’ dialogue).
- Add-in: ‘Professor Excel Tools‘ provides a button for saving single or selected worksheets as new XLSX files with just one click (attention: INDIRECT formulas might display #REF! errors).
File type: XLSX – Scope: Complete workbook
- Automatic: Press Ctrl + s for saving the file under the current file name and folder or F12 for changing the file name or folder.
- Add-in: ‘Professor Excel Tools‘ provides a button for saving complete workbooks as new files.
Free Download: Get all these tips summarized in one printout
We’ve prepared a handy printout: All the important information given in this article are summarized within one comprehensive PDF file. You can download it with this link (file size: 1.4 MB). The download starts immediately (no sign-up required). Just print it out and follow all the steps described in this article.
The guide comprises the steps for adding the necessary buttons to the Quick Access Toolbar. Furthermore, it tells you which saving method should be used in which case with a clearly structured matrix.
Taking it one step further: Save both PDF and Excel files at the same time with just one click
So we now know how to save Excel workbooks either as PDF or as XLSX files to the hard drive. But we haven’t talked about how to do both at the same time.
- Manual: There is no built-in automatic or semi-automatic feature for saving both PDF and Excel files at the same time. That means that you have to save both files to the hard drive (see the methods above) separately.
- Add-in: ‘Professor Excel Tools’ offers one-click-buttons for saving both Excel and PDF files at the same time to a new e-mail.
‘Professor Excel Tools‘ provides 6 buttons for saving files. In the left column, you got the buttons for saving single or a selection of worksheets. With the buttons of the column on the right hand side you save the complete workbook.
The rows also follow a systematic order: The button of the top row are for creating PDF files, the middle row for the Excel format and the bottom row for both PDF and XLSX at the same time.
You can try the add-in for free for 31 days. There is no sign-up or installation required (only activate it within Excel).