July 5, 2023
3 Excel Tricks You Should Be Using
Whether you like it or not, Excel is here to stay. For those in the finance and accounting industry, Excel experience is a necessity—even if you know a little bit or a lot. Since Excel won’t be fading into the sunset any time soon, here are some Excel hacks you may not know of.
Custom lists:
Sometimes you need to sort text using different parameters besides alphabetization. For example, you may need to sort text by organizing it by days of the week or months. This feature takes a bit more time to set up, but once you do it for a set of data, it’s easy to use that data in the future. First, go to File > Options > Advanced. Under General, click the button “Edit Custom Lists.” Enter your list, then click OK twice to get back to your data. Next, select the data you want to sort and go to Data > Sort. Under Order, select Custom List. Select the custom list you want to use and click OK twice to get back to your (now custom-sorted) data.
Fixing V lookup:
You can see that the two values match perfectly, but the “V LOOKUP” or “MATCH” formula still says there is no match. Extra spaces are often the culprit here. If the text has leading or trailing spaces, Excel does not classify it as an exact match and your formula can return unexpected results. A potential solution is to use the TRIM function. This function removes any leading and trailing spaces and any extra space between words.
Know how to debug:
Missed a comma, wrong reference, missing argument, wrong parenthesis position? The list of possible causes of formula errors could be endless. Debugging a formula could be painful, especially if it has been created by someone else. What you may not know is that there is a fairly painless way to debug a formula. Select the cell that has the formula. Go to Formulas > Formula Auditing > Evaluate Formula (Keyboards Shortcut Alt + TUF). Then click on Evaluate to see if the steps the formula produced is evaluated by Excel.
