5 Essential Excel tips for your AP department

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.

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.

Flash Fill step 1 Excel

 

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.

Flash Fill Step 2 Excel

Continue this is in subsequent columns.

flash fill step 3 excel

 
 
2. Turning Your Spreadsheets Into Tables

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.

Format Table Button

From here you can sort the columns using the drop-down arrows. It also makes your spreadsheet easier to read!

blue table excel

 

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.

Format Numbers Button

  • 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.

Find and replace function excel
 
 
 

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.)

Copy Visible Cells - Step one

To copy only the visible cells, select the cells that you want to copy.  

Click Home > Find & Select, and pick Go To Special.

Find & Select Button

Go To Special Button

Click Visible cells only > OK.

Paste Visible cells only button

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).

Clipboard button Excel

 

what other tools and short cuts do you use in excel? 

Keep your system. Change the way you make payments.