7 Essential Excel Hacks That You Must Know

Written By: Joshua Tan

If you’re a student on vacation and have recently taken up an admin or data entry job , you might appreciate some quick Excel tips to make your job easier!

Being tasked with compiling and extracting information from spreadsheets can seem daunting, but with these tips by your side, you'll be able to navigate the world of Excel a whole lot better.

Problem #1: Your supervisor asked you to combine First and Last Names in a spreadsheet into one cell or merge different portions of an address into a single text string, so that they can send out e-mails and letters more efficiently.

Use CONCATENATE. This lesser known function allows Excel users to merge text from different cells into a single text string. This is extremely useful for merging names and addresses. Remember to also add spaces with “ ”.

Step 1: Identify the column you want your results to appear and then click function. Step 2: Search and select CONCATENATE in the function menu. Step 3: Select the cells you want to combine. Step 4: Create a space between your result by inputting " " in between the cell formula. Step 5: Drag the formula down the rest of the column for it to also apply to adjacent cells.

 

Problem #2: Your boss needs you to do the reverse of CONCATENATE and split full names to first and last names in separate columns so that they can archive the data by the alphabetical order of last names.

Use Text to Columns. This function essentially splits a single text string into separate columns. An example is shown in this YouTube video, where full names are split into first and last names in separate columns. Other delimiters include dashes or commas. Alternatively, you can also draw fixed width lines to split text.

Step 1: Select the cell you want to split and click 'Text to Columns' under the data tab.

Step 2: Select 'Delimited' and click next.

Step 3: Select your delimiter (what you use to separate your results); in this case a space separates the results, so select that.

Step 4: Select the 'General' data format then click on the destination tab.

Step 5: Click on your destination cell then click on the destination tab again.

Step 6: Voila!

Problem #3: The company that you work for has a ton of data that needs sorting and the spreadsheet feels like it scrolls on forever. You have way too many columns of data and scrolling down means you don't remember the column header. You don't want to keep scrolling back up, too.

Use Freeze Panes. Be it columns or rows, this function means you don't need to keep scrolling back and forth just to know what you're looking at.

Step 1: Select 'Freeze Panes' on the view tab.

Step 2: From here you can freeze the top row or the first column, or both.

Step 3: To do that, select a cell adjacent to the rows and columns you want to freeze. In this case, it is B2.

Step 4: Click freeze panes on the menu and you're done!

Problem #4: Your boss wants you to compare the sales performance of David the salesman against his peers. But the data seems to be everywhere!

Use Split. Excel allows you to split a spreadsheet into two or four, each with their own scroll bars. By doing so, you can effectively view up to four different points of a spreadsheet at the same time - making any form of comparison work a breeze!

Step 1: Select the cell directly beneath David's. The sheet will split the adjacent cells once the command is completed.

Step 2: Select 'Split' under the view tab.

Step 3: You can now scroll through the rest of the sheet with David locked in place!

Problem #5: You're not a math whiz and it will take you a long while to compute the averages, sums and counts of the data you're given.

Look carefully on the bottom right corner of the Excel window. Every time you click two cells or more, the following appears beside the zoom bar: AVERAGE, COUNT and SUM. This easily missed function can quickly tell you the result of essential mathematical operations you might need to compute in your work. If the cells you want to compute are adjacent, simply hold Ctrl and click on the cells you want. In addition, you can also use the same words as formulas at the bottom of columns you want to compute. In this example, we want to quickly find the average of January's sales by Tom, Dick, and Harry:

Step 1: Ctrl + Click on Tom's, Dick's, and Harry's sales numbers.

Step 2: The results are at the bottom of Excel, next to the zoom and viewing format tool.

Problem #6: You want to quickly arrange your data in alphabetical order or by price value and you need all the adjacent cells to rearrange themselves as well.

Use Sort & Filter. This function has a wide variety of uses: from sorting names by alphabetical order to sorting prices by lowest to highest. What really takes the cake is Excel's ability to sort things by several orders of criteria. For example, if you would like to sort data first by alphabetical order for column A and then by lowest to highest value for column B - it can be done! So let's say your boss wants you to find out who had the best sales for January and February, here's how to do that fast!

Step 1: Let's arrange the names in alphabetical order first. Highlight the name column and select sort and filter under the home tab. Then click sort by A-Z.

Step 2: Next select 'Custom Sort...' from the drop down menu.

Step 3: Select January's sales, sort it by volume, and to the appropriate order. Then click add level and do the same for February.

Step 4: Viola.. the winner in January seems to be Shanti!

Problem #7: Someone designed a spreadsheet that needs data to be input left to right, but since hitting Enter causes the active cell to move downwards, you have to keep using your mouse.

Change what happens when Enter is hit. Who knew that this could be resolved! All you have to down is go into the settings and modify it. It's that easy and your lives will be transformed. You can now enter data in any direction you like.

So perhaps in this case, your company has the sales team pass you their monthly numbers and keying them in is tedious as the Enter button moves you to the bottom cell, instead of to the right. Here's how to fix that:

Step 1: Click on 'File' and then 'Options'.

Step 2: Click on 'Advanced' and you can change the direction from the option highlighted in the screenshot.

With these excel hacks, you'll have a head start in your next office job over your peers. You'll also get better on the job the more you use them. Check out the part-time and full-time office jobs we have on FastJobs.

Download the FastJobs app for faster browsing on the go!