Save time with Microsoft Excel!

In parts 1 and 2 of this blog, we looked at the first six tips for making you more efficient:

  1. Get to know the shortcuts
  2. Name your ranges
  3. Get to know your options
  4. Use the Insert Function button to get help with functions
  5. Use the Trace Precedents / Trace Dependents buttons
  6. Use the Evaluate Formulas button

If you missed the articles, you can find them here – Top 10 Microsoft Excel Tips (Part 1) and Top 10 Microsoft Excel Tips (Part 2)

In this final part – tips 7 to 10 – we’ll take a look at tips for working with large sets of data, as well as a last “genenral” tip for Excel as a whole

Tip 7: Keep your headings where they are on the screen

If you have 5000 rows of data, it’s a safe bet that you won’t be able to see all of them on the screen at the same time. And when you’ve scrolled down past the first couple of hundred, it’s easy to forget which column is which when the headings have long since disappeared off the top of the screen.

Similarly, you may have a column on the left containing customer names or account numbers which you’d like to be able to see even when you’ve scrolled to the right.

In either case, you’ll need the Freeze Panes tool. In Excel 2007 and 2010, Microsoft have made this rather easier to find – firstly, there’s a large button on the View tab, labelled “Freeze Panes”, and secondly, it contains nice clear options for “Freeze top row” and “Freeze first column”.

 

It’s also possible, however, in earlier versions of Excel – take a look in the Window menu and you’ll see “Freeze Panes”. But this doesn’t make it very clear exactly which panes (or more usefully, which rows or columns) will be frozen. And that’s also the case when choosing the first option in Excel 2007 / 2010.

The rule of thumb is this: when choosing “Freeze Panes” in whichever version of Excel, Excel freezes content above and to the left of your selection.

So, if you want to freeze both the top row AND the left column, you’ll need to select cell B2, and then choose Freeze Panes – the row above and the column to the left of your selection will be frozen – that is, column A and row 1. If you want to freeze the top two rows, select the whole of row 3, and click Freeze Panes.

Tip 8: Repeat your headings on every printed page

Whereas tip 7 allowed your headings to remain visible when looking at later rows of data on the screen, tip 8 achieves something similar for the printed page.

I’ve seen people who need to print 200 rows of data, get out the glue and scissors and then stick the resultant 4 printed pages together, so that they can easily see how the headings at the top relate to the data further down! Maybe that’s you? In which case, put away your paste – there’s an easier option.

 

Microsoft Excel allows you to specify that one or more rows should appear at the top of every printed page, and that one or more columns should appear at the left of every printed page. In order to get to this setting, you need to open the Page Setup dialog box.

This is perhaps easier in “old” Excel than “new” Excel. In Excel 2003 or earlier, go to the File menu and select Page Setup. In Excel 2007 or later, you need to go to the Page Layout tab on the ribbon, then click the small arrow at the bottom-right corner of any of the groups here. This arrow is the “dialog launcher”. In the screenshot to the right, you can see that I’ve clicked the dialog launcher in the Page Setup section, then I’ve clicked on the Sheet tab of the Page Setup dialog box, but in fact you could click the dialog launcher in the Sheet group and it will take you straight to the right page of the dialog box.

Once you’re here, look for the settings “Rows to repeat at top” and “Columns to repeat at left”. You can either type a value directly in here, or you can click in the appropriate field, then click on the row number / column letter back in the main Excel window to specify where your headings exist.

One last word of note about this dialog box.

You can get exactly the same dialog box from the Print Preview (or Backstage in Excel 2010) screen. But if you access the Page Setup dialog box from within Print Preview, you’ll find that some of the options – including the one we want – are greyed out and unavailable. So, if you find that’s the case, you’ll need to return to the main Excel window, then open the Page Setup dialog box from the File menu or the Page Layout tab on the ribbon as appropriate.

Tip 9: Use Custom Lists

You may be aware that when you type Monday, then use the fill handle (the black dot in the bottom-right corner when you select a range) to drag it down or to the right, Excel is smart enough to give you Tuesday, then Wednesday and so on.

And if you knew that, you probably also knew that if you type January then fill that down, Excel will give you February, then March. And the same applies to the shortened versions – you can type Mon to get Tue and Wed or Jan to get Feb and Mar.

But what’s slightly less commonly known is that you can “teach” Microsoft Excel any list you like, and it will remember and use it in the same way as it does for the months and the days.

So, if you’ve got a list that you constantly have to enter – be it office locations, staff names, countries, part numbers, steps in a process – whatever it may be, you need never enter it again.

Open a file which contains your list of items in successive cells of a range. If you haven’t got such a file to hand, just enter the values in a range on a blank worksheet.

Next, select those cells where you’ve typed the values you want Excel to remember.

Then, open the Custom Lists dialog box. Where this is depends on your version of Microsoft Excel:

  • In Excel 2003 and earlier:
    Go to the Tools menu, then click on Options, and select the Custom Lists tab at the top
  • In Excel 2007:
    Click on the Office button, then choose Excel Options. In the Popular category (from the list on the left) you’ll find Custom Lists.
  • In Excel 2010:
    Click on File and select Options. Then, click on the Advanced category on the left. Near the bottom of these options, you’ll see a button labelled “Edit custom lists.”

As long as you selected the cells containing your list of entries before launching this dialog box, you’ll be able to click the Import button to bring in your list automatically.

At any stage, you can now edit your list simply by returning to this dialog box and overtyping or adding to existing entries, and if it’s no longer relevant you can remove the list entirely by cicking the Delete button.

To use the list, simply type any entry (it doesn’t have to be the first one, just as you can type “Tuesday” and fill, not just “Monday”) and then fill it as needed.

One last word of advice on this tip. You need to ensure that every entry is unique across all lists. So, if you’re making a list of staff, and you happen to have a staff member called April, add her to your list as April S, if she’s April Smith, for example. Otherwise, Excel will not be able to determine which list you want to use, and if you type April and fill it expecting to get May and June, you’ll be disappointed…

Tip 10: The best tip of all…

The very best…

The one that will improve your Microsoft Excel skills beyond all recognition…

If you want to experience the ultimate in Microsoft Excel training, and you want to have training that’s tailored to your own needs, built around your data… call The IT Service on 01483 277172 or visit The IT Service Excel training page and talk to us about how we can help you!