15 Essential Excel Tips and Tricks for Finance Professionals

Starting my journey as an intern at a CA firm, Excel quickly became more than just a software program—it became my necessary tool for analytical and audit tasks. Despite completing an Excel course, applying that knowledge practically in the field presented its own set of challenges.

If you are a new joiner, this article is a great place to start. Hopefully this article has something for everyone or boosts your confidence if you are an Excel wiz.

Note: I will assume you have a basic understanding of using Excel, such as “=SUM(..."), freeze panes, filtering, sorting data, etc., so I will skip these.

1. Pivot Tables

My first encounter with Pivot Tables was nothing short of transformative. Picture this: a spreadsheet full of a year's worth of financial transactions. Pivot Tables allowed me to effortlessly extract vital data, like total amounts per account code each quarter. It was like magic, making complex data manipulation feel almost intuitive.

Key Tips:

• Ensure there are no blank fields/columns when creating pivot tables.
• Refresh Pivot Tables to reflect changes in raw data promptly.
• Practice makes perfect—experimenting with Pivot Tables enhances proficiency.

2. Pivot Chat

Pivot charts visualize the data expressed in a pivot table, giving us insight at a glance.

3. VLOOKUP

VLOOKUP, a function I first learned about during my Udemy Excel course, proved invaluable in merging data from different sources. Whether sorting customer details or matching revenue figures across scattered sheets, VLOOKUP streamlined my tasks, consolidating information seamlessly.

Key Tips:

• Handle errors like "#N/A" by checking formulas and data consistency.
• Format data consistently for accurate VLOOKUP results.
• Use absolute references ("$") to prevent data loss when extending formulas.

4. Data Formatting

Dealing with messy, unstructured data was a challenge until I discovered Excel's data formatting tools. From text to columns for splitting cells to removing duplicates effortlessly, these tools turned chaos into clarity, making data analysis easy.

Key Tips:

• Use Excel Add-ins like the Excel Analytics toolbar for advanced data management.
• Master features like deleting blank rows and converting text to columns for cleaner datasets.

5. Quick Access Toolbar

Customizing my Quick Access Toolbar was a game-changer. Adding shortcuts for filters, format painter, and other frequently used tools made navigating Excel a breeze. It's these personalized tweaks that saved me valuable time and frustration.

Key Tips:

• Tailor the Quick Access Toolbar to your workflow for maximum efficiency.
• Explore lesser-known shortcuts to enhance productivity, like tracing precedents and dependents.

6. Other Useful Functions

While I may not use every function daily, knowing them broadens my Excel toolkit. Functions like CONCATENATE for combining text or TRANSPOSE for flipping data orientation can be lifesavers when handling diverse data formats.

7. Adjusting Rows

A lesser-known trick I discovered is adjusting row heights for entire workbooks at once. Highlighting all desired columns and rows and adjusting one set of uniform sizes across the board is a real time-saver for meticulous formatting.

8. Drop-Down Lists

Though not a daily tool, drop-down lists ensure data consistency and minimize errors in inputs. Creating them through "data validation" from the "data" tab simplifies data entry, particularly in structured environments like financial analysis.

9. Conditional Formatting

Honestly, I think I use conditional formatting more than any other feature in Excel. It's just so handy! Conditional formatting lets you highlight or hide cells based on rules you set. You can apply these rules to a single cell or to multiple cells in the same worksheet. It's great for spotting outliers, duplicates, or patterns in your data. By using conditional formatting, you can quickly see trends and anomalies, making your data analysis way easier and more insightful.

10. XLOOKUP

XLOOKUP is like the best of both worlds from VLOOKUP and HLOOKUP, since it can search both vertically and horizontally to find a value in a range. It lets you use a selected range as a lookup table and return the “looked up” result to a cell. Here’s the syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Imagine you want to look up the year of birth based on an entered ID value. Start by entering an ID value—say, 8755—in cell AD2. Then, in cell AE2, type the XLOOKUP formula:

=XLOOKUP(

Here’s a step-by-step breakdown:

• lookup_value: This is the value you want to look up. Enter AD2.
• lookup_array: This is the column or row that contains the lookup value. Enter A2:A2241 to search the array of IDs.
• return_array: This is the column or row that contains the value you want to return. Enter B2:B2241 to get the Year_Birth values.

So, the completed formula looks like this:

=XLOOKUP(AD2, A2:A2241, B2:B2241)

Now, when you enter different IDs in cell AD2, the corresponding Year_Birth will appear in cell AE2.

Lookup functions are incredibly powerful. You can even join data from different sheets or nest lookup functions within each other. For example, you could sum the value of multiple lookups.

11. IFERROR

The IFERROR function is a lifesaver when it comes to handling errors in Excel. It lets you create a custom error message when a formula doesn’t work as expected. For example, you can use it to wrap your XLOOKUP function so it returns a clear message if the ID you’re looking for isn’t found. The syntax is pretty straightforward.

=IFERROR(value, value_if_error)

Let’s say you’re using the XLOOKUP function, and you enter an ID into cell AD2 that doesn’t exist in your lookup array. Normally, you’d get an #N/A error in cell AE2. But with IFERROR, you can make it return “ID Not Found” instead. Just use the XLOOKUP function as the value in IFERROR. It should look something like this:

=IFERROR(XLOOKUP(AD2, A2:A2241, B2:B2241), "ID Not Found")

What’s cool is that you can return more than just text. You can point the value_if_error to another cell, too. If you target a blank cell, Excel will display 0 in the cell instead of an error.

12. RANK

The RANK function orders a number by its size relative to other values in a list and returns the desired rank. This means the rank of the number corresponds to its position if the list were sorted in ascending or descending order. For example, if we sort income in descending order, the largest value will be at the top, and that could be rank 1. RANK gives duplicate numbers the same rank but skips subsequent ranks accordingly. This means if two values are ranked four, the next rank will be six, not five (1, 2, 3, 4, 4). The syntax for RANK takes three arguments:

=RANK(number, ref, [order])

It’s important to note that [order] can be set to 0 for descending and 1 (or greater) for ascending.

Let’s say we want to rank the income (column E) of our customers. Use the following formula in cell AH2:

=RANK(E2, E2:E2241, 0)

To apply the formula to all rows, click the square at the bottom right corner of cell AH2 and drag it down to the last row of data. This will automatically copy the formula, allowing us to quickly generate a rank for each income value.

13. MATCH

Similar to lookup functions, the MATCH function is handy when you need the position of a value in a range instead of the value itself. Here’s the syntax:

=MATCH(lookup_value, lookup_array, [match_type])

Understanding which match type to use is important. Although it's optional and defaults to 1, the available options are -1, 0, or 1:

• -1: Finds the smallest value that is greater than or equal to the lookup_value.
• 0: Finds the first value that is exactly equal to the lookup_value.
• 1: Finds the largest value that is less than or equal to the lookup value.

Let’s say we want to find the first time the looked-up birth year occurs. Enter the following MATCH formula in cell AF2:

=MATCH(AE2, B:B, 0)

This will return the position of the birth year value in column B that matches the value in AE2 exactly.

14. Excel Shortcuts

15. Some Star Excel Shortcuts

Recently, I've come across some Excel shortcuts that have been absolute lifesavers. One of my favorites is "Ctrl + [].". This shortcut allows you to trace precedents in a formula, which is incredibly useful when you're working with complex calculations. For instance, if a cell contains a formula like "=(C18*11)", using "Ctrl + [" will instantly take you to cell C18, showing exactly where that number comes from.

Another handy shortcut is "Ctrl + Shift + +". Despite its unusual appearance, pressing the "+" key at the end adds rows or columns. Simply highlight the row or column where you want to insert the new one, use the shortcut, and there's your freshly added row or column.

One shortcut that I recently shared with a friend turned out to be quite appreciated. It's "Ctrl + down arrow", which swiftly navigates to the bottom cell of a column without scrolling endlessly. This same shortcut works with the up, left, or right arrows. Add "Shift" into the mix with "Ctrl + Shift + down arrow", and it highlights all cells down to the bottom of the column.

Lastly, there's "Alt + Tab". While I personally don't use it often, this shortcut switches between different Excel files you have open, making you look like a true Excel wizard.

Conclusion

Excel isn't just a tool; it's an ongoing journey of discovery and mastery. Every new tip and trick enhances my efficiency and deepens my understanding. If you found these insights helpful, show some support by liking my article.