Hate Spreadsheets? 9 Tips for an easier life

This is a series of little tips. I may find more than 9 that are quick hitters but for now this is the first of 9 mini articles to Learning to Love Spreadsheets

There are many versions of spreadsheets, paid and free, and to be honest these past few years I have ditched the paid versions for the freebies, of which include LibreOffice and Open office for Desktop and Google Sheets and Zoho Sheets for online.

The free ones have come on in leaps and bounds, especially the online ones, that there really is no reason to pay for a good spreadsheet platform unless it is bundled with something else that you do need to pay for. For me I now pay for my Zoho account because of it's integrations and automations. But they have a few free apps including a Word Processor called Writer and free accounting Software called Invoice

I will admit, if you are into some serious financial modelling and scientist level number crunching then you can't beet Excel, but, for us mere mortals who work in an office and not a research lab, there is probably nothing that the free online ones can't do for you.

BUT...

Common handy tools

Regardless of which platform is your favourite there are some common tips that I am always sharing with clients that I forget is not second nature to some people. I will admit I am a bit of a geek when it comes to spreadsheets and get them to do an awful lot of heavy lifting when it comes to data, so sometimes forget not everyone are as in to spreadsheets as I am.

So I have pulled together some of my handy GoTo Tools I take for granted but would be lost without, and I think if you try them out you might actually start to fall a little bit in love with the much hated but misaligned spreadsheet.

Freeze Column / Row

Continue a Series

Named Range & Fixed References

Conditional Formatting

Drop-Downs

Filters

Lookups

Pivot Tables

Macros

These can be all done in all spreadsheets I have come across so far, but the sake of explaining how to find things I will be using our favourite tool Zoho Sheets, which is free along with it's brother Zoho Writer

So 1st in the series and is probably the most simple handy tool is...

Freeze Column / Row

You have a long list of information, and as you scroll down or to the right you lose your column labels or row headings

So where you start off seeing this...

Once you have scrolled down a few rows, and checked the columns over to the rigth you start to see this ...

Not quite so handy without the headings.

If however you head up to 'View' in the menu and choose Freeze, you can fix the first column and the first row so that no matter where you are in the spreadsheet you can see the labels and headings.

And you will notice that in this example you also have the option to freeze up to row 21 and column F, or both. This is because I have my cursor in cell F21 and is giving me the option to free more than one column or row.

When I choose that option you can see where it freezes the columns and rows.

Obviously I wouldn't want to do that normally, but in this instance I might want more than just one column or row, I might want both the Works Order and the Dates visible along side the headings. So if I place my cursor at the point I would like everything fixed

You can see I now have 3 columns and one row frozen, so I can scroll all the way down to the bottom right and still know what I am looking at.