Two additional advanced Excel tips to simplify your work
In this article, we gave you four advanced Excel tips. Now we're going to give you two more tips so you can work smarter and faster.
Advanced Excel tip #1: Borrow table formats for your Excel data
itbusiness suggests you use these two advanced Excel tips if you use Excel spreadsheets:
Using the 'Format as Table' command on the 'Home' tab of the Ribbon toolbar applies a neat format to a list of data in Excel. Unfortunately, it also adds filters to your column headings and changes the behaviour of columns and rows adjacent to the list.
If you want the convenience and simplicity of applying table formats with a single click, but don't want the other features applied at the same time, you can easily remove the extras.
To see this at work, select your list, including the heading row. Apply a table format to it by selecting the 'Format as Table' option on the 'Home' tab of the Ribbon. Click a table style to apply it to your list. When the Format As Table dialog box appears, select the 'My table has headers checkbox', then click OK. Your data will then be neatly formatted.
To remove the other features that Excel adds automatically to your list, keep the list selected. Choose 'Table Tools' and then the 'Design' tab and select the 'Convert to Range' option.
When Excel asks, 'Do you want to convert the table to a normal range?' click Yes. This action removes the filters from the column headings, and removes other table-specific behavior, but leaves the formatting in place.
PS: Do you know why Gail doesn't spend R10 000 a month on bookkeeping fees?
Gail is a GP who's just opened her own private practice. Because she's trying to save money on admin costs, she is doing her books herself. She thinks: 'How hard can it be?' The answer: 'It's very hard unless you've got the right tools at your fingertips!' Well, I found just such a tool. It has everything from checklists about how to prepare for an audit, to detailed explanations about financial statements. Find out more about my newly-discovered one-stop accountancy resource!
Advanced Excel tip #2: Keep an eye on data with a watch window
When you're editing a very large worksheet, often you need to view how your changes in one area affect totals and other calculations in cells that currently sit off-screen, either further down the page or on another worksheet tab.
Instead of repeatedly moving back and forth from your working data area to the cells that show the results you're interested in, use a 'Watch Window' to display the values of the cells in the other part of the worksheet as you tinker.
To set up a Watch Window, click the left mouse button within one cell that you want to watch. From the Ribbon toolbar, choose 'Formulas', Watch Window. When the Watch Window dialog box appears, click 'Add Watch'. Because you've already selected the cell, you simply need to confirm that the reference to it in the dialog box is correct, and then click Add.
Now return to the area of your worksheet that you're editing. As you do so, you'll see that the Watch Window floats atop the worksheet, allowing you to work on your data. While you edit, the Watch Window shows the other cells, reflecting the changes you're making. You can move and resize the Watch Window as you wish, and use the Add Watch button to view additional cells in the Watch Window.