Excel Novice 5 min tip – Quickly access frequently used files and folders
If you need to quickly access frequently used files and folders, use the File | Recent option to find them. Click on File, click on Recent. You will find a list of recently used files and folders. Annoyingly, this feature is not available in Excel 2013 and later…
If you want to keep a permanent link to a file or folder…pin it. You do this by simply clicking on the pin icon. This keeps this file or folder at the top of the list until you “unpin” it.
Excel Adept -Using Power Query to clean up your data
If you are an accountant or if you have ever downloaded accounts data (in order to do a pivot table on it), well then you know you have spent many happy (ha!) hours cleaning it up, removing totals, removing blank rows and columns to get it to a pivot ready state. And then of course you have to do it again….So what I want to cover in this tutorial is show you how to
- Clean up an accounts file (Clean_up_accounts_2013) using Power Query . Check the totals of this file (It’s in cell R1494 Amount = 155,664.75. (T1494 has the Net Amount=155,664.75. VAT Amount=27242.70 is in cell V1494 and the Gross Amount is 182,907.45 in cell X1494)
- Note that in both cases the files have been converted to a table first (this ensures that Power Query will pick up all the transactions even if the second file has more rows..)
Weird thing…I had real problems with this file. It kept rounding the numbers up to whole numbers. So I ended up doing the following:
- Copy/Paste Values for the Amount, Net Amount, VAT and Gross Amount columns. I then converted those numbers to currency. Then even when I imported this into Power Query I converted these columns again to Currency to make sure they showed two decimal places.
- What I also found frustrating was that it was only when I pulled in the data directly from the file into Power Query that it kept the decimal places. So unfortunately in this tutorial I won’t be able to show you how to re-use the same code (that’s only available if you pull the data from a file into Power Query)
Here are the steps
- Open the file called Clean_up_accounts_2013). Make sure you have clicked in the table in the sheet called Accounts
- Navigate to the Power Query tab
- Click on From Table.
- This will pull it into Power Query.
- Now highlight the Amount, Net Amount, VAT Amount and Gross Amount columns and convert them to currency. Home | Data Type | Currency.
- Remove columns 1,2,3,4,5,6,7,8 9,10,11,12,13 by highlighting them, then pressing the Ctrl key to select the ones afterwards. Then do a right click and click on Remove Column
- Highlight the Type column, then click on Tranform – Fill – Down
- Do the same for Name, Account, Memo and Class.
- Filter to remove the nulls from the Date column
- Last step, click on Close and Load to bring it back into Excel.
11. Highlight the Amount column. Check the status bar. Yep, it 155,664.75. Do the same for Net Amount. Yep, it’s 155,664.75. Ditto for VAT Amount. Yes, its 27242.70 and the Gross Amount is 182,907.45