HomeHome SearchSearch MenuMenu Our productsOur products

Excel spreadsheets: Use these two advanced Excel tips to simplify your work

by , 10 March 2014
If you use Excel spreadsheets, you'll be glad to know that there are two advanced Excel tips you can use to simplify your work. Continue reading to find out what they are.

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.

Related articles

Related articles

Watch And Learn

Related Products


Recommended for You 

  Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance

Here are all the most interesting, thought-provoking and common tax questions
asked by our subscribers over the last tax year – everything from A to Z!

To download Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance click here now >>>
  Employees always sick? How to stop it today

Make sure you develop a leave policy to regulate sick leave in your company.

BONUS! You'll find an example of the leave policy and procedure in this report.

To download Employees always sick? How to stop it today click here now >>>
  Absenteeism: Little known ways to reduce absenteeism

This FREE e-report will tell you how you can reduce absenteeism in your workplace while avoiding the CCMA and without infringing your employees' labour rights.

To download Absenteeism: Little known ways to reduce absenteeism click here now >>>
  7 Health & safety strategies to save you thousands

Don't let a health and safety incident cost you one more cent. Implement these seven
strategies in your company today.

To download 7 Health & safety strategies to save you thousands click here now >>>