Are you someone who struggles with spreadsheets?
Are they too big and very data heavy with data scattered all over them?
There are many ways you can make these easier on the eye and also make the data you need to find more visible using simple basic features that are in all versions of Excel.
Here we’ll go over just a few of the simplest ways to make your spreadsheets easier to understand and read.
This can make a big difference if you have a lot of data and the data you need is nearer the bottom of the spreadsheet. You can select which column you want do the sort on, this is great if you want to search on a part number, surname or postcode field for example. You can choose whether that column is in ascending order or descending order and you can also create a custom sort where you can tell it to sort on one column first and then another column after and you can add more levels to this sort so that you can sort the data in multiple columns making it even easier to find information.
These are a great way to narrow your search down even further, by simply highlighting the row with the headers in it and then clicking on filter at the top, on the ribbon, you can quickly add in drop down fields. By clicking on the arrow to the right of the header and then selecting one of the options you bring all the information you need to the top of the spreadsheet, once you’ve finished editing you just clear the filter by clicking select all again.
This is a great way to update information that’s all related at once, for example, if you were a beauty shop retailer you could filter on shampoo or a specific brand of shampoo and then update all the information for that particular product/brand, this way you are capturing all the necessary data that needs updating/amending.
If your Excel version 2010 or newer you can also filter by colour. This is something we use a lot. As an example, we had a spreadsheet that listed all the orders we had placed for clients, it had all the different order numbers (theirs and ours) and we knew how much we were expecting on the invoice from the supplier. This information was all colour coded with one of three colours, one to say order was placed, one for invoice received and one to say we’d recharged the client.
By using the filter by colour option, we could quickly see at a glance how many invoices had been passed to the finance team and how many we had recharged, so we could see what money was due in very soon. Again, it’s a very simple and effective way to see where you are currently at with finances/data, especially useful if you need the information to reconcile accounts each month or annually or use the information for a report or dashboard.
Most people we come across know of these but don’t necessarily know how to use them or understand how they can make things easier for them. Freeze panes is one of the functions we use the most. They’re great if you have a long spreadsheet and you keep adding information to the bottom, by clicking on freeze top row you can keep the column headers visible at all times, making it easy for you to see what the column relates to. You can also select to freeze the first column, this is particularly useful if you have data you always need to see on the left side, for example a name or employee ID number.
The other option is to insert the cursor in the cell below and to the right of the cells you’d like to freeze on, this is really useful if you want to keep the headers in view and you also need to see the first few columns of data, by moving the bar along the bottom you move all the data on the right hand side of the frozen columns, which is great if you have lots of data to complete and need to keep certain cells in view – this is one of our favourites.
As mentioned, these are just a few examples of tools in Excel that can make things much easier for you.
There are many other things we could show you to make your spreadsheets easier to manage. Take a look at our services page to see how we can make this a reality for you.