7 Excel functions and shortcuts that save me hours every day

Excel can be a time sink. If you are tired of wrestling with VLOOKUPs, filtering data, and text manipulation, it’s time to adopt these key Excel functions and shortcuts. From complex data lookups and dynamic filtering to rapid text manipulation and swift financial calculations, I have curated a selection of powerful functions designed to streamline your spreadsheet tasks in no time.

But it doesn’t stop there – I will also share a collection of time-saving keyboard shortcuts that will have you flying through your spreadsheets.

7

XLOOKUP

VLOOKUP who?

XLOOKUP has replaced INDEX/MATCH and VLOOKUP for me in most scenarios. Unlike VLOOKUP, you don’t need to count columns. You can specify the lookup and return arrays directly. Besides, it can look up values to the left of the column, which VLOOKUP cannot do.

Let’s say you are managing a sales database for an online store and you have a couple of tables, ‘Product details’ (Product ID, Product Name, Category, and Price columns) and ‘Sales data’ (Order ID, Product ID, and Quantity Sold columns) on your spreadsheet. Now, you want to add the Product Name and Price to the Sales Data table.

You can create a couple of columns for Product Name and Price on ‘Sales Data’ table and use the XLOOKUP function to find the Product ID in the lookup array and return the corresponding Product Name and have the price for each product sold.

6

PMT

Fly through your loan calculations

The PMT function is designed to calculate the periodic payment for a loan (assuming constant payments and interest rates). It’s a fundamental tool for financial calculations in Excel. When you create loan amortization, plan investments, do financial analysis, or lease calculations, use PMT to get the job done.

Suppose you took $200,000 loan for 30-year mortgage with an annual interest of 5%. You can use the formula below to calculate your monthly payments.

=PMT(0.05/12, 30*12, 200000)

5

FILTER

Filter your data in no time

FILTER function is a powerful tool for reporting and data analysis. It is often easier to use than complex combinations of INDEX, MATCH, and IF. It can handle complex filtering logic with multiple conditions, and the syntax is relatively easy to read and understand.

Suppose you are a project manager using Excel to track your team’s tasks. You have created a database with columns like Tasks, Assignee, Status, and Due Date. Now you want to check which tasks Bob is working on.

=FILTER(A2:D7, B2:B7="Bob")

Excel will display a table containing only the rows where the Assignee column is Bob. Similarly, you can filter ‘In Progress’ tasks, ‘Completed’ tasks, and even display a message when there are none.

Related

7 quick Excel tips to convert a beginner into a pro

Skyrocket your Excel skills

4

TEXTBEFORE and TEXTAFTER

Extract text and valuable information

These two Excel functions let you extract text that appears before or after a specified delimiter. You can extract first name, last name, and domain name from an email address and even file names without extension.

If you have a cell containing Parth Shah, and you want to extract Parth. You can run =TEXTBEFORE(A1, ” “), and it will return Parth. Similarly, you can run =TEXTAFTER(A1, ” “) to extract Shah.

3

SEQUENCE

Start your spreadsheet quickly

This is another Excel function that I wish I knew earlier. The SEQUENCE function generates a sequence of numbers in an array. This is quite handy for creating dynamic lists, generating dates, and automating repetitive tasks. For example, if you want to create rows with numbers 1 to 30, you can simply type =SEQUENCE(30) to get the job done. You can also type =SEQUENCE(5, 3) to create a 5×3 table of numbers. The possibilities are endless with this one.

2

UNIQUE

You can use the UNIQUE function to extract a list of unique values from a range or array. It is quite useful when you want to remove duplicates and create a distinct list. Suppose you have a giant Excel spreadsheet with hundreds of customer names in a column. Now you want to extract unique customers from the list.

You can choose an empty cell (B1) where you want the unique list to appear. Now, use =UNIQUE(A1:A100), and Excel will write down the unique customer names in the cells below B1.

1

SWITCH

Apply relevant logic

SWITCH is a powerful logical function that evaluates a single value against a list of values and returns the result accordingly. Suppose cell A1 has a numerical month (1-12). You can now use the formula below to convert it to the month’s name.

=SWITCH(A1, 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "Invalid Month")

Similarly, you can assign a day of the week (converting 1-7 numerical to weekday) and even turn product code into a category using the SWITCH function.

My favorite Excel keyboard shortcuts

Now, let’s go over some of my most used Excel keyboard shortcuts.

• Ctrl + T: Turns the selected range of cells into an Excel table.
• Ctrl + 1: Open the Format cells dialog box.
• F4: To repeat the last action.
• Shift + Space: Select the entire row where your current cell is located.
• Ctrl + Space: Select the entire column where your current cell is located.
• Ctrl + ;: Adds current date to the cell.
• Alt + =: Performs an AutoSum.

Unlock ultimate efficiency in Excel

I know how much time I used to waste on tedious Excel tasks, and discovering these functions and shortcuts completely changed my workflow. Now, I hope you’ll experience the same. What are you waiting for? Experiment, apply them to your real-world tasks, and watch your efficiency soar.

Aside from these functions and keyboard shortcuts, I have also started exploring Python in Excel to fly through my tasks. Check out my dedicated post to learn more.