When Microsoft released Excel 2013 it dropped one functionality that I really, really miss … Save Workspace, available in version 2010 in the Window group on the View tab. It allowed you to save several workbooks you needed for a particular task in a special workspace file (*.xlw) so that when it was time to work on the job again, you just opened the workspace file and all files would be restored in the same arrangement you left them.
Like the idea? Why not record a macro that does just that? It might look daunting, but you only have to do this once and you can save yourself a lot of time in the long run.
Here’s how (in Excel 2013 and beyond):
1. If necessary, add the Developer tab to the ribbon by right-clicking anywhere on the Quick Access Toolbar and selecting Customize the Ribbon. (I know I normally don’t add screenshots, but I frequently encounter people who don’t know what I mean when I talk about the Quick Access Toolbar.) Then, on the right hand side of the window, tick the Developer check box under Main Tabs.
2. On the Developer tab, in the Code group, click Record Macro.
3. Type a name for the macro, such as workspace or several. (Avoid using the name Open.)
4. Press TAB and hold down, say, the SHIFT key and type the letter O to assign CTRL + SHIFT + O as a keyboard shortcut to run the macro.
5. Press TAB and select Personal Macro Workbook to make the macro available whenever you use Excel. (Make sure to click Save when you close down Excel and see a prompt to save the changes you made to the Personal Macro Workbook.)
6. Click OK.
7. Open the files you want be able to open in one go.
8. On the Developer tab, in the Code group, click Stop Recording.
In future, you can use the shortcut key you assigned in step 4 to open the various workbooks.
Bonus tip: why not add the macro as a button on your Quick Access Toolbar?
1. Right-click anywhere on the Quick Access Toolbar and select Customize Quick Access Toolbar.
2. In the Excel Options dialog box, under Choose commands from, click on Macros.
3. In the left list, double-click the newly created macro.
4. Press ENTER or click on the OK button.
By the way, you can still open a workspace (*.xlw) file that was created in an earlier version of Excel.
Upcoming 60-minute Excel webinars
Want to see these sort of tips in action? Why not sign up for one of the following dates:
Thursday 27 August 09:00 - 10:00 Book now
Tuesday 8 September 10:00 - 11:00 Book now
Price: £20 + VAT = £24
Related tips
Customizing the Quick Access Toolbar
Selecting commands without using your mouse
24 August 2020
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk