Familiarity with Microsoft Excel is essential for day to day work as an AP professional. As useful as this product may be, the hundreds of functions and interface can quickly become overwhelming. Here are several pointers to help you maximize your productivity in Excel.
1. Flash Fill ⚡
Flash fill allows you to quickly copy data into new columns and rows without having to copy or type it all out. It’s a fast and easy and way to clean up data. Excel is able to detect patterns in your initial data entry, and then Flash Fill is able to figure out the data you want to copy over. The series of entries appear in the new column in a flash 😉.
To use the Flash Fill feature, navigate to the Data tab, in the data tools box there is a flash fill button.
To use this tool, first you need to tell Excel what you want to do by entering the value “600 Galleria Pkwy.," into cell B1.
After you show excel the specific information you want to be filled in, press this button and the column will fill. You can also get the same effect by using Ctrl+E.
Continue this is in subsequent columns.
Creating tables in your spreadsheets makes it easier to sort and select specific rows and columns.
To start, highlight your entire spreadsheet and select Format as Table in the Home tab.
From here you can sort the columns using the drop-down arrows. It also makes your spreadsheet easier to read!
3. Formatting Numbers and Dates
Excel will automatically format numbers and dates.
To format cells as numbers or dates, use the Number function in the Home tab or by highlighting the cells you want to format and pressing these select formulas.
- 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
4. Using the Find Function 🔎
One very simple but useful tool is the Ctrl+F function. Pressing this brings up the Find and Replace tool.
Using this you can quickly search for specific words, numbers and characters in a large document. You can even replace those characters all at once using the replace feature.
5. Copying Visible Cells only
When you filter subtotal or highlight cells in Excel the copy and paste function over range suddenly doesn’t work how you'd like. Cells that are hidden or moved suddenly become visible when you paste them.
(For this example we've hidden the Street and City columns from the example above.)
To copy only the visible cells, select the cells that you want to copy.
Click Home > Find & Select, and pick Go To Special.
Click Visible cells only > OK.
Click Copy (or press Ctrl+C).
By doing this you will select only the cells that are visible.
The next step is to paste the copied cells where you want them. To do this, electing the upper-left cell of the paste area and click Paste (or press Ctrl+V).