Microsoft Excel has never been considered the most exciting software, but it has many useful tricks up its sleeve. In this article, we're sharing some Microsoft Excel hacks you should know about if you want to make the most of its functions, from eco-friendly printing to freezing panes.
Most of us will use the basic functionality of the Microsoft Office Suite, rarely straying from the path to explore what else it can do. This is quite understandable, we don’t know what we don’t know, and we think we have all we need. However, there are loads of Microsoft Excel hacks that can make life that little bit easier.
Air IT helps clients to unlock the potential of their IT infrastructure. To that end, we want to show you just how productive ‘productivity software’ can be. Here are some useful Microsoft Excel hacks you may have never known about.
Prevent accidental edits with Cell Protection in Microsoft Excel
If you are sharing important data in Excel documents, there are a number of ways that you can avoid accidental deletion or amendment of a spreadsheet’s contents. However, if you want to protect a specific group of cells, rows or columns and allow the rest to be changed – here’s how.
Begin by turning on the Protect Sheet function. To do this, click the Format drop down on the right-hand side of the Home ribbon, then click Protect Sheet. The window that opens will allow you to customise the protection settings. Choose the modifications you want to prevent other users from making – then input your password and confirm.
Next select the cells, rows, or columns you wish to protect, then click the Format drop down again and click Lock Cell.
This then means that any time a user tries to make edits, they will receive an error message informing them that they must request a password to make any edits to the cells you’ve protected.
The trick to printing spreadsheets in Microsoft Excel
Printing spreadsheets is a popular bugbear for staff. Spreadsheets tend to work well on monitors, which have become gradually wider over the years, but don’t always translate when you click Print – and the result can be an unusable printout and a waste of ink and paper.
Firstly, the columns within the sheet itself can be manually adjusted to make the spreadsheet as a whole more suited to printing. For example – long column titles can either be shortened or the text can be wrapped to make the title fall over two lines – halving the width of the column. By doing this, you can make spreadsheets far more print-friendly, but there’s more that can be done.
In the name of making your IT ‘greener’, here’s how to avoid waste and create user-friendly spreadsheet printouts.
Excel’s printing options offers a solution with its Backstage. These options let you adjust the paper size and orientation. Spreadsheets are often best printed in landscape orientation – but it depends on the spreadsheet. If the number of columns appears to lend itself better to a portrait view, don’t fight it.
Adjusting margins can allow you to fit additional info on a single sheet. To do this, click the Show Margins button in the bottom right-hand corner of the backstage print tab. Adjust the margins to fit data which might spill over to another page.
If you are still struggling to fit the data on one page, you can use settings within Page Setup to tweak scaling. The scaling menu can help you to reduce the size of the text to make the spreadsheet fit. It’s important to find a balance when scaling the spreadsheet however, as scaling it too small will make the text illegible.
Of course, if you have the ability to print in A3, this can be very advantageous for wide spreadsheets and can provide room for annotation.
Making the most of printing spreadsheets in Microsoft Excel
As a follow-on on how to make your spreadsheets fit, here are a few things you can do to make the most of your spreadsheets and make them as user-friendly as possible:
- Print titles – these can be displayed both at the top or on the left-hand side.
- Page numbers – these can be inserted into the footer of the page. By incorporating a page number in the form of “Page _ of _” it can be easier for readers to keep track of where they are in the spreadsheet.
- Logos – you can insert your logo(s) into the header and footer to brand the spreadsheet.
Freeze Panes allows you to stay focused on a particular part of a spreadsheet whilst scrolling through the sheet’s other information. This is useful if you are manually analysing data, particularly if the data is a large sample.
To use this function, Select the column or row where the data you wish to review begins.
Then select the View tab, at the top of the window, and click Freeze Panes.
Now, when you scroll, the headings or columns you wish to keep in place will remain visible and in place with the information you’re scrolling through moving below or to the side of it.
Excel can Autosave every minute
The last of our Microsoft Excel hacks is for those of us who spend hours meticulously creating spreadsheets, worrying that if the computer crashes, you could lose valuable work.
Thankfully, Excel is capable of Autosaving as frequently as every minute, so the most you could possibly lose is a minute’s worth of work.
To make this adjustment, click the ‘File’ tab then select ‘Options’ at the bottom left. When the ‘Excel Options’ window opens, select the ‘Save’ tab and you’ll find the “Save AutoRecover information…” line. The editable field will usually say ‘10’ minutes, you can then adjust this to ‘1’ and now Excel is Autosaving your work every minute.
A potential drawback with this change is you could see a slowing of your computer. If this happens, then you can incrementally increase that AutoRecover interval to ‘2 minutes’, ‘3 minutes’, or until you find the best increment for you.
Spending too much time on Excel?
Microsoft Excel hacks are useful and the software definitely has its place, but the abundance of data we have access to nowadays means that it isn’t possible to make use of it all manually. Our business intelligence services can save you time and money by automating your reporting and providing a single source of truth for your data – to find out more, please don’t hesitate to get in touch.