5 Basic Excel Functions That Make Work Infinitely Easier
As an accountant, I love excel and would love to know everything there is to know about excel and what it can do, but sadly this is some more study for another time. For now, here are some basic functions that may come in handy for you, written by Fatima Wahab.
Auto-Complete Series & Auto Fill Formulas
Entering data, particularly a long set of data is time consuming with an excellent chance of a human-error occurring. That’s why, where possible, Excel predicts the text you’re trying to enter and gives you the option to fill it in for you.
Take for example you want to enter serial numbers or Employee ID numbers that follow a sequence. Instead of manually entering them, you can enter the first three or four numbers, select the cells with the numbers entered. Bring the cursor to the bottom right of the last cell and wait for it to turn into a black plus sign. Drag it out and you will be able to see the value each cell will hold. Excel will automatically increment the value for you. This trick is best used with formulas because the formula will auto-update to read the corresponding cells in a row instead of having an absolute value that copy/pasted formulas would have.
This trick is bound to be picked up while you enter data. Excel can auto-fill data in a cell if the data you’re entering has been entered in a previous cell in the same column. Just hit enter and to fill it in.
Smart Alphabetical Sorting
Excel can sort data alphabetically but a beginner might hesitate to use the sorting option because data in adjacent columns is related to it and reordering the data will disturb the relationship. For example, you have names, departments, addresses, phone numbers, and blood groups all in individual columns of their own. You want to sort the names in alphabetical order but still want the adjacent cells to read the correct address, phone number, and blood group for that individual.
You can go ahead and use the Sort A to Z option because Excel will ask if you want to expand the selection to include data in adjacent columns. This lets you sort one column alphabetically and have the corresponding data in other columns sort itself respectively. This smart sorting works for any type of rules you might use.
Transforming Row Data To Column Data
A lot of times, data isn’t even in the correct layout for anyone to begin analyzing it. The format that it was collected in wasn’t created with any specific purpose in mind and you might often find that you need to transfer data from a row to a column or vice versa. Instead of copying and pasting each individual value, you can use a Paste Special function called Transpose to do just that.
Select and copy the column you want to paste as a row. Select the first cell of the row you want to copy the data to. Click the little arrow under the Paste button and select Paste Special. In the option box that pops up, check the ‘Transpose’ option and click OK.
Separate First And Last Names
Excel 2013 has a really smart auto-fill function that works between columns. Take the very common example of a column that contains both first and last names. You might want to separate the first and last names and in older Excel versions, this meant using the concatenate function in a very clever/tricky way.
With Excel 2013, all you need to do is start typing the first name in the next column. Do this for the first two or three entries and Excel will learn what your separation criteria is and list the data for all corresponding values in the column for you. All you have to do is hit enter. This works for just about any type of data.