For many people, a spreadsheet is a tool for making lists or a way of adding a column of numbers or just something to be avoided altogether. I think spreadsheets are very underrated.
“Whatever you think of the spreadsheet don’t dismiss it as trivial.” Mike James, I programmer “Spreadsheets are special”
My challenge to you is to see what more you could be doing with spreadsheets.
Microsoft released the first version of Excel spreadsheets for the Macintosh in 1985, it’s changed a lot since then! It is a tool that lets everyday people use code to manage data in a really simple way. Google Sheets and Apple Numbers are really similar and let you do pretty much the same things, they are just called different names and of course, functions are kept in different places to confuse us.
Heather Donahoe from First Point Consultants, delivers training on the different Microsoft products, including Excel spreadsheets. She says often there are gaps of knowledge that the self-taught don’t know and many times she hears “If I learn nothing else today that shortcut is going to saved me so much time”
Heather says there are three Problem areas that spreadsheets address:
- Collecting data
- Analysing data
- Visualising data
Collecting data
Each square, or cell, of a spreadsheet, can be typed into with text or with numbers that can be used for a calculation. Formulas can be entered to add, subtract, multiply or create complex calculations. With formulas you can do many things including merging text, dividing text or removing spaces.
In the example below, you can see I have created a list of data values that can be selected from. This is really useful because it means only these options can be entered and so you won’t end up with a selection of entries which might include various spelling, typos and those with random spaces at the end which make it difficult when you come to filter the data later on.
Another really useful tool when entering data is Flash fill. For example in the table above I have delegate names, first and last names together. Say I want to separate them into a column of just first names, maybe so I can personalise messages to each of them. I can start typing the first names down the column (E in the example). From the home menu I can choose Fill, Flash fill and it will finish the entries for me. This can be used so long as there is a recognisable pattern to copy.
Things spreadsheets can be used for
- Budgets/finances – tracking spend or forecasting expected spending so you can budget
- Contacts/CRM – a record of your clients and contacts and their relevant information. Create a list so that you never miss an annual review, deadline or miss someone off the Xmas card list. If you have a CRM system you will probably be able to export the data into a spreadsheet, then you can filter the information to meet your needs.
- Records e.g. inventory – keep track of your stock and predict when you need to place orders
- Project plan/task list – details of the tasks you need to complete and their deadlines. If you are working with others you can also show who has the action and mark when it is complete.
- Record of achievements – for example, you could record the distance you run each week, the books you read or the places you have travelled to.
- A mailing list for mail merge – if you have a lot of people to send letters, or emails, to you can record the names, addresses and other relevant details that can then be pulled into placeholder fiends in letters
If you have data in a separate spreadsheet that you want to refer to, you can link to other worksheets in your excel workbook or even to a different spreadsheet file. This means that you can bring in information, without making your spreadsheet too big or without sharing the raw data with others, if you don’t want to.
Analysing data
Spreadsheets are made up of rows and columns that make up tables of data. The data can be sorted by different headings, for example, A-Z, highest-lowest or filtered so you can select to show only the row of data for one category.
In the example below you could filter by each heading; Course, Location, Date etc. but also the Delegate column can be filtered by colour to pick out those in red.
Excel formulas and tools to transform spreadsheets
Conditional formatting can be used to highlight cells, e.g. the top 3 highest-numbered items, all items above average or duplicate entries. In the example above the pink cells in the Follow-up column indicate a date for action, you could flag anything due or overdue.
Visualising data
Now comes the fun part. You can transform the rows of data in your spreadsheet into a visual representation. If a picture is worth a thousand words, instead of rows and rows of data you can show the results in an image instead.
Simplifying reports, dashboards & data tracking
Data-driven decision making in business is so much more effective because it is based on facts. If you know the trends in your year/month you will be able to more realistically forecast sales and react accordingly.
- Goals – Everyone should have something to aim for so make a tracker for your goal and see your achievements as you reach them
- Time – do you have a work/life balance? Keep a record of the hours you work on your business as well as in your business so that you know your true hours.
- Budget planning – compare your budget amount to the actual spend and income, formulas can tell you if you are within +/- 10% tolerance or if you are up or down on last year.
What’s a pivot table anyway?
Microsoft says “A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.”
Essentially a pivot table is a way to view key parts of your data with extra filtering. In the example below, you can see the courses and number of delegates, but this pivot table means you can also change the location and or date to further interrogate the data. This is a very simple example but Pivot tables can be used to quickly show essential information that you may want to report on.
Graphics
Once you have a table of data it is really easy to select the information that you want to present and turn that into an image. Sparklines can give a really simple view of data performance, as in the example below.
For more complex data, highlight your cells so you can convert it into a graph. There are loads to choose between and you can tailor them. Change the font, use your brand colours, alter the layout and the way the legends look etc. so they always look the same. Your graphs will change with any new data you add so you can see the current situation.
Once you have made your graphs you can share them with others, for example, add them to a presentation or create as an image to put on social media, ‘8 out of 10 cats…..’
Save time
Macros
If you export data from a system and carry out the same steps each time, for example, to add filters, total the amount in columns or just improve the overall look, you may want to use a macro. This is simply a recording of the steps which, once set-up, can be performed again and again at the touch of a button.
Templates
Another way to save time is for any spreadsheets you produce regularly. For example, if you use spreadsheets for your invoices or reports, a template will mean they look the same each time, with matching header/footer, brand font, colours and logo.
Let me take a weight off your mind
So, if you want help producing spreadsheets or managing the ones you already have. If you would like advanced formulas, pivot tables or graphs. Even if you would like a macro or a templates document created, Contact me. Hand over your spreadsheets and I’ll transform them for you.
For more guidance: