As an AP Professional, you probably rely on Microsoft Excel for much of what you do in a typical workday. As helpful and essential as Excel can be, it’s many features and functions can be overwhelming. There are countless commands and shortcuts within Excel designed to help streamline your spreadsheets, but you may not know which ones to focus on. The CSI has a few Excel tips for AP professionals that we recommend working into your repertoire to save time and maximize productivity.
BACK TO THE BASICS
Our number one Excel tip for AP is to simply feel confident working in the application itself and explore what Excel has to offer. Familiarize yourself with the menus, ribbons, and all the other tools at your disposal. Excel makes it very easy for you to learn more complicated formulas without actually having experience with them. Many of your questions can actually be answered within the program itself.
If you go to the Formulas ribbon you can find any formula you need based on category. For example, if you select the ‘Lookup & Reference’ library and choose ‘vlookup’ it gives you an easy to use dialog box that breaks down and explains the formula step-by-step for you. It even offers a hyperlink to further help elsewhere.
COPYING VISIBLE CELLS ONLY
This Excel tip for AP pros is one that you may find yourself using regularly once you become familiar with it. If you filter, subtotal, or hide rows/columns you will find that using copy/paste over ranges that have hidden cells don’t behave as you would like; all cells get included in the copy/paste even though you can’t see them. While you can’t fix this for the ‘pasting’ (this will always paste in consecutive rows/columns) you can prevent this from happening when copying.
“Go to Special>Select Visible Cells Only” is a tool that selects only the cells that you can actually see. This means that when you copy a range with hidden cells, the hidden cells won’t be copied. This is a super handy tool and anyone that works in Excel every day knows the frustrations of trying to copy without knowing about this tool.
This tool can be reached in 3 different ways:
- Alt +; while highlighting the range.
- F5 -> ‘Visible Cells Only’ while highlighting the range.
- The Home ribbon (shown below).
DETECT ALL CELLS LINKED TO A FORMULA
If you are dealing with complicated formulas, sometimes it can be hard to tell where all the numbers in the formula are coming from. There is a great shortcut that can drill down the formula and show all the cells that are linked to that particular formula.
- Highlight all the cells that have a value and then use CTRL+Shift+{
- The cells that are contained in the formula will remain highlighted.
FORMATTING NUMBERS
If you want a number formatted in a specific way (such as a date, time, percent), you have to inform excel this is the way you want the number to appear. Most people manually highlight the cells and then change the formatting using tools. While that works, here are a list of shortcuts you can use instead:
Formatting with two decimal places: CTRL+Shift+1
Format as Time: CTRL+Shift+2
Format as Date: CTRL+Shift+3
Format as Currency Value: CTRL+Shift+4
Format as Percentage: CTRL+Shift+5
Format in Exponential Form: CTRL+Shift+6
FLASH FILL
Flash Fill is a relatively new feature in Excel that gives you the ability to quickly take content from your existing data and move it to new columns, without having to construct or copy any kind of formula. The moment Excel detects a pattern in your initial data entry, flash fill is able to figure out the data you want to copy over. The series of entries appear in the new column, literally in a flash. It is great for cleaning up data and can be very fun to use.
Rather than manually entering first or last names in respective columns (or attempting to copy an entire client name from column A and then editing out the parts not needed), you can use Flash Fill to quickly and effectively do the job. Here’s how you do it:
Extract Data: Use Flash Fill to extract the numbers in column A below.
- First, tell Excel what you want to do by entering the value John into cell B1.
-
- On the Data tab, in the Data Tools group, click Flash Fill. Or you can use the shortcut CTRL E.
Result:
Join Date: Use Flash Fill to join the first names in column A and the last names in column B to create email addresses.
- First, tell Excel what you want to do by entering a correct email address in cell C1.
2. On the Data tab, in the Data Tools group, click Flash Fill, or use the shortcut Ctrl E
Result:
Looking for more ways to save time on AP processes? We have a few more tricks up our sleeve including AP automation techniques that can transform your AP department into a payments powerhouse and revenue generator.
