10 Simple Excel Tips and Tricks To Make Work (And Life) Easier

Different industries, different roles, different sets of skills. After all, we don’t expect doctors to master the practice of law. Just as we don’t require advertising experts to perfect flying an aircraft. Regardless, one thing’s sure: Almost everyone needs Microsoft Excel to be brilliant at what they do these days.

Are you one of those professionals likely to benefit from Excel tips and tricks? Keep on reading for Excel formulas that you should know.

Also read: The Top 7 Skills In Resume For Fresh Grads To Include!

Why You Should Master Excel

Put simply, most of us handle data on a daily basis. A restaurant business would need a file where they can keep their day’s earnings. Marketing firms require well-organized details of their promotional work. Financial analysts rely heavily on a computer program that makes calculating data easier. For all of these functions, Microsoft Excel pretty much is there to save the day.

Once, people used Excel to mainly do data storage and basic calculations. But today, the program has become highly innovative that practically anything that you need to perform your tasks at work can be done on this tool. Eventually, when you master Excel and make it something you’re proficient in, chances are it’ll make you that much more productive.

With that being said, we’ve listed down some of the most important Excel tips and tricks that you will need. Maybe not just for work, but in your day-to-day life too!

Also read: Get Your Tech Skills Ready For The Job Market

Excel Formulas That You Should Know

All in all, there could be hundreds of ways to use Excel like a pro. Here, however, we’ll try to focus on basic Excel formulas that make work easier, as recommended by professionals across various industries. These comprise techniques that, although not as advanced as what most highly specified industries use, go a long way once you make it a habit of using them.

The basics

1. Shortcuts

No matter what it is you are using Excel for, it’s always a good idea to start with learning frequently used Excel shortcuts. They’re deemed as such because not only do they eliminate the need to manually search for the correct tools in Excel. The following are just a few of the ones worth remembering:

• To search or locate data by keywords, use Ctrl + F.
• To copy the data to the cell directly below, use Ctrl + D.
• To select or highlight all cells, use Ctrl + A or Ctrl + Shift + Spacebar.
• To hide certain rows, use Ctrl + 9.
• To hide certain columns, use Ctrl + 0.
• To apply a fill color, use Alt + H, H.
• To apply borders to a cell, use Alt + H, B.
• To add or insert a note, use Shift + F2.
• To add a new line in a cell, use Alt + Enter.
• To auto-enter the current date or time, use Ctrl + Semicolon or Ctrl + Shift + Colon, respectively.

2. Functions and formulas

Although dated to some, functions are still one of Excel’s most unique and essential features. Unlike using a calculator, you can save time transferring numbers between your working file and your calculator by doing everything in one place. This effectively eliminates tampering or erroneously copying values for more efficient data management or analysis. But which functions to focus on?

Obviously, that depends on what you intend to compute. But the following functions should be extremely handy at all times:

• To add up numbers from different cells, type “=” + SUM.
• To obtain the average value of numbers, type “=” + AVERAGE.
• To find the maximum or minimum value in a section of cells, type “=” + MAX or MIN, respectively.
• To determine the number of cells containing the same data, type “=” + COUNT.
• To round up or down numbers, type “=” + ROUND.

3. Replicating formulas

You might already know that Excel has its own way of computing numbers for you. Simply type the “=” sign, choose a function (e.g. sum, average, etc.), and then select the cells that you want to calculate using that function. But what if you need to do the same calculation for different cells but want to save yourself the time of applying the formula every single time?

To do this, you’ll simply need to highlight the cell where the original formula was applied, then click on Copy or Ctrl + C. Once you get a hold of the data, paste it into the designated cells. Excel then automatically adjusts the value according to where you place it in the spreadsheet. To make things even more convenient, you can also just drag the highlighted cell by clicking on the small box with the “+”.

4. Highlighting cells

Did you know that Excel has a maximum limit of over 1,000,000 rows and 16,000 columns? Imagine an instance wherein you’re actually asked to fill all those cells, or at least need to highlight them. If you started using the program as everyone else did, you probably thought there’s no other way to do it than to simply keep on scrolling until you’ve reached the very end. How long could it possibly take you?

As Excel is meant to make your work hassle-free, it also offers a simple hack to make selecting or highlighting cells take only a few seconds.

First, go to the cell where you want the selection to start from. For columns, press Ctrl + Shift + the right or left arrow on your keyboard, and it will lead you all the way to the last column of the sheet. For rows, do the first two commands followed by the up or down arrow, depending on where you want the selection to go.

The advanced

5. SUM versus SUBTOTAL

As mentioned earlier, one of the most common functions in Excel is the SUM, as literally, anyone would need to get the total amount of select data at some point. Simply type “=” in a designated cell followed by typing or choosing SUM. The total of the highlighted cells would then appear in that cell just like a typical calculator.

However, there are times when you need to filter some information and would need to only include select values. Yes, you can use another simple shortcut of using Ctrl + pressing only the cells you want to include in the calculation. But when you’re using the filter feature of Excel, a quicker trick is to use the Subtotal function. First, filter the cells. Then, in a designated cell, type “=” along with SUBTOTAL, and instantly, only the values of your chosen information will be calculated.

6. AutoFill and Flash Fill

Similar to replicating formulas or functions, AutoFill and Flash Fill work by predicting information when working with several cells. Say you want to input dates in chronological order without manually typing them for each cell, AutoFill will add them for you. Just type the first date on the first cell and hover your cursor to the bottom left of the cell until it turns into a black plus sign. Click on it and drag it to applicable cells.

A more advanced version of AutoFill is Flash Fill, which debunks any claim of Excel being suitable only for numerical data. For example, you have a range of cells containing the names of clients but are formatted differently. To apply the same format for all, type the correct format in the first cell, then choose Flash Fill on Data. Or, press Ctrl + E highlighting the cells, and you’ll see a more coherent appearance of your data.

7. PivotTables

Want to summarize your data to make them more understandable and appealing to look at? Use PivotTables. This feature is primarily a table or chart that compiles data in a concise and presentable manner. Super convenient when dealing with huge heaps of information! With well-organized data, tracking and analyzing them will be far less complicated.

The shortcut for creating PivotTables is Alt + N + V. If you are working in human resources, administration, or management of employees, and want to compile the performance of an employee for a month, you can create your own PivotTable to narrow down the data to that of your selected employee. Then, you can further adjust them according to what you only need for a time.

8. VLOOKUP

For Excel starters, it's often tempting to use the usual Ctrl + F when finding something on a page. But what if you are handling large amounts of data across different spreadsheets? Moreover, do you want to do more than simply locate pieces of data?

Literally standing for Vertical Look Up, the VLOOKUP function helps you to look for specific information in your file. It also provides you with relevant data associated with it. It’s called Vertical for a reason too. That is, it analyzes the sheet from the leftmost column to the right, making it suitable only for certain structures of data. One of its biggest draws is that it returns you with the latest data. This way, you don't need to keep updating the same information across sheets.

9. INDEX and MATCH

A newer yet less popular way to look up values is INDEX and MATCH. Basically, it addresses some of the restrictions of VLOOKUP, but it’s also far trickier to use. So whether you want to use one over the either really depends on how fast you want to get information. Or what kind of data you want.

INDEX and MATCH combines two separate Excel formulas, the INDEX along with MATCH. In contrast to VLOOKUP, you don't need to arrange your data from the leftmost column. In fact, it analyzes both horizontal and vertical data, as well as updates them even when you add or delete a new row or column. This makes INDEX and MATCH a better option when looking at complex information. To use it, type =INDEX (array, row number, column number), MATCH (return value, array, match type).

10. CONCAT

Need to construct a new statement based on separate materials? What about simply compiling random data together to make a more meaningful one? If yes, then the CONCAT formula is exactly what you need. It replaces its predecessor CONCATENATE to provide you with a more convenient way of managing sheets.

One of the main differences between CONCAT and CONCATENATE is that it allows joining texts together using a range of cells. Whereas, the older version will only work if you manually add the relevant cells. What's more, CONCAT can add up to 255 text values or "arguments" and return up to 8,192 characters. If you need to exceed beyond that, you'll only need to perform a new formula on a different cell.

Want To Learn More Essential Work Tips?

Are you itching to apply these Excel tips and tricks to your work (and everywhere else)? #LetsGetToWork now!

For more helpful advice on your workplace, download the JobStreet app on Google Play or App Store, then visit Career Advice. Once you’ve mastered the above Excel skills, use them to your advantage in future job applications. Create or update your JobStreet profile here today!