Posted On 15 Apr 2021
You can’t beat using Excel when you’ve got serious quantities of calculations to handle, but we all know how boring and time-consuming it can be to get all the data you need into your spreadsheets. Fortunately, there are a couple of ways that you can try to shortcut your way into saving time and effort.
It’s often necessary when creating a spreadsheet to separate combined data into different cells, for example if you have a list of names you will often need to have first names and last names in separate cells for data handling purposes. Rather than retype them all, try using Flash Fill to achieve your aims.
This is how you do it:
• Enable Flash Fill by clicking on File and selecting Options
• In the Options dialogue box, select Advanced from the list down the left-hand side
• Tick the check box marked Automatically Flash Fill and click OK.
• Start inputting your list of first names
• Once you’ve put in three or four names, select those names and drag the selection box down to the bottom right; if all works as it should, Excel will recognise what you’re trying to do and populate the rest of your list.
Bear in mind that Flash Fill isn’t perfect by any means, but it frequently does work and could save you an awful lot of time, so it’s well worth a try.
Another potential timesaver is the Auto Fill feature. This can identify when you are trying to create a data list that has a specific pattern to it, and it can take over a lot of the spadework.
Imagine, for example, that you want to create a list of cells containing the numbers 1 to 10. Simply enter 1 into your first cell, press enter, select the cell and hover your mouse over the bottom right corner until your cursor turns into a +. Then simply drag down 10 rows and let go. If all goes according to plan, you’ll have your numbered list. However, sometimes Excel doesn’t quite get it right and you might end up with 10 boxes all filled with the number 1. If this happens, simply go to the bottom right corner of the selection again and click the Auto Fill Options button and select Fill Series.
If you’ve got a more complicated pattern of data, e.g. increasing in increments of 0.75, just give Excel a little more information by entering 0.75 in the first cell, 1.50 in the next, 2.25 in the next, and then dragging down.
Auto Fill can handle other data formats as well, such as dates, so, for example, if you need a weekly list, fill in the first few dates, e.g. October 11, October 18, October 25, and drag down, Excel will continue the series.