This was a question posed on Linked in the other day. However, maybe the question should be how long do you spend working effectively in Excel each day? Could your time be used more efficiently by using the correct functions and shortcuts. Are you spending ages copying and pasting or re-entering data wondering if there is a quicker way?
To help with your spreadsheets here is a step by step plan for working with data. Follow these rules, and you will have a spreadsheet that will work properly and be bullet proof for further data analysis and reporting.
- Think about the columns you require. What data will be included in that column. Is it going to be figures, dates or text? Keep the data consistent – don’t mix figures and text otherwise Excel won’t be happy when you try to total the column.
- Give each column a relevant and effective heading.
- Don’t leave blank rows and columns between data. Excel needs to understand that the data in your list belongs together. Do not try and separate it with the use of blank columns or rows. If you follow this rule, your filters and sort will automatically include all the data.
- Make your data a table. Click in your data. Use the shortcut CTRL T. This will apply formatting, freeze the header row and apply filters. Any columns or rows you add will automatically become part of the table.
- Never merge cells. If you merge cells within a table, Excel does not understand which column that data is part of. Therefore ilters won’t work beyond the merge cell. Rather then merge cells, incorporate an extra column with that information in. That way you can filter on that criteria as well.
- Dates – never use dots in dates other Excel will see this as text. The best way to enter a day is using a forward slash eg 7/10/2019.
Want to find out more.