Make your spreadsheet a joy to behold with a few simple set up stages
Before we eat dinner we set the table, so that the crockery, cutlery, condiments, drinks etc. are ready for us when we sit down to eat.
In the same way, we can prepare a spreadsheet before we sit down to work.
With everything laid out neatly on view, your spreadsheet will be easier to use and your work more of a pleasure.
So, if you don’t want to make a dogs dinner of it, here are some really simple tips for preparing a tasty looking spreadsheet!
You’ll find when you add a lot of columns to your spreadsheet that you need to scroll to the right to view all the information. When you do this the columns on the left disappear from view.
The same is true of rows. As you scroll down the page, your header row will eventually disappear from the top.
The first columns and rows in your spreadsheet often contain key information, so it is useful to have them showing all the time.
Did you know you can “freeze” some of the columns at the top, or on the left-hand side? When frozen the columns remain in view when you scroll down or to the right.
To freeze columns:
Click on a cell to mark which columns or rows you wish to freeze
To freeze rows at the top, click on the cell just below the row(s) you want to freeze.
To freeze columns on the left, click on the cell just to the right of the row(s) you want to freeze.
If you wish, you can freeze rows and columns at the same time
Select Window from the menu and then Freeze Panes
Watch this video to see the Freeze Panes technique in action:
Make your columns wider
The standard width of the columns in a spreadsheet is sometimes too narrow to hold the information you want to include.
Making the columns wider will allow you to enter more information without overlapping into the next column.
Here’s how to quickly adjust the width of several columns:
Click and drag over the grey bar with the letters on it to select your columns
Hover your cursor over the grey bar above either of the lines between the columns
When you cursor is in the correct position it will change to a symbol with a line and two arrows pointing in opposite directions
Now click and drag your mouse to adjust the width of the columns
Zoom to achieve the best view
If you find yourself squinting or overwhelmed by large print, you probably need to adjust the Zoom.
The Zoom enlarges or reduces the size (or magnification) of your view.
You can adjust the Zoom in two ways.
- On the toolbar you’ll see a white box with a percentage in it (the default is 100%). You can adjust the magnification by clicking on the tiny arrow beside the percentage box and selecting larger or smaller percentage.
- Alternatively you can go to the View menu, select Zoom then choose the level of magnification.
One of the most effective ways to Zoom is to “zoom to fit selection”. This means the screen will be magnified just enough to display all the information you’re working on.
To do this, first click and drag over the area you’re working on, then do one of the following:
- On the toolbar, click on the down arrow next to the percentage, then select Selection
- Go to the View menu, select Zoom, then choose Fit Selection
Get your spreadsheet ready for print
Even if you run a paperless office, one of your colleagues may wish to print your spreadsheet in due course.
If your spreadsheet is set up for print right from the start, life will be easier when it comes to printing down the line.
What needs to be printed?
The Print Area is the part of the spreadsheet that will be printed:
To set the Print Area
Click and drag your mouse over the area to be printed
From the menu, select File, then Print Area, and finally Set Print Area
Personally, I like to select whole columns for my Print Area. This means that when I add any new rows in future they will be printed too.
If you want to check which cells are included in your Print Area, go to the small white box just above and to the left of column A (with letters and numbers in it). Click on the tiny arrows to the right of this box and select Print_Area. Your Print Area will now be selected.
Preparing your spreadsheet for printing
Printing is a big topic and one I plan to cover in a future blog post.
In the meantime, here are a couple of tips for making your spreadsheet print out in a sensible way.
To set up your page for printing go to the File menu then select Page Setup
The Page Set Up screen will open. The Page Set Up screen contains tabs at the top for Page, Margins, Header/Footer, and Sheet.
Making your spreadsheet fit the page
On the Page tab of the Page Set Up screen, there is an area where you can set up the Scaling.
Scaling reduces or enlarges your spreadsheet so it fits neatly on the page when printed.
If you have quite a small spreadsheet, I’d recommend setting the Scaling to Fit to: 1 page wide by 1 page tall. That way your whole spreadsheet will appear on one page.
You can adjust this setting if you think your spreadsheet will be particularly tall or wide.
For a tall spreadsheet, with a few columns but lots of rows, you might select 1 page wide by 10 pages tall.
Whereas a wide spreadsheet, with many columns but few rows, might be better suited to 10 pages wide by one page tall.
You can also set the Orientation of the page here (to landscape or portrait). You may wish to adjust the Orientation if your spreadsheet is wider than it is tall (or visa versa).
Printed out spreadsheets are much easier to read when there are lines (gridlines) around each cell; but by default Excel spreadsheets will print out without any gridlines.
To add gridlines to your printed spreadsheet:
Go to the File menu then select Page Setup…
The Page Setup screen will appear
Click on the Sheet tab at the top of this screen
Now click on the check box next to Gridlines so that a tick appears
Previewing how your print out will look
Rather than waste paper, you can preview on screen how your print out will look so you can adjust any print settings before printing.
The way you do this depends on which version of Excel you have; so try one of the following:
Go to the File menu and click Print Preview
Go to the File menu, click on Print, a screen will appear which includes a preview of your printed spreadsheet
On the ribbon at the top of the screen click on Layout then click on the Preview button