Kayleigh - 03 Feb 2006, 12:50 pm
1 Get together
Sometimes you might need to combine characters or numbers that are in different cells. Janet Waweru, a Part 3 student from Kenya uses concatenation to make this task easy. ‘To combine characters or numbers in different cells use: =concatenate(specify the cells to combine). For example, if you have the individual letters of janet in a row of cells you could join them together using =concatenate(A1, B1, C1, D1, E1) in the cell that you want them to be joined together in.’ You can also use the ampersand (&) symbol to join cells, for example =A1 & “ ” & B1 will join together cell A1 and cell B1 with a space (denoted by “ ”) in between.
2 Look it up
‘At work, I recently used VLOOKUP, which I found really useful,’ says Damree Mohammad Allyhim, a Part 3 student from Mauritius. ‘Say you need to fill your income statement and balance sheet in Excel from figures in your trial balance. VLOOKUP can help you to avoid laboriously retyping figures that already exist in your trial balance (extracted from accounting software to Excel). You just need to use VLOOKUP for the accounts code (normally located to the left of your description, eg sales) and the function will automatically insert your figures into the income statement and balance sheet.’
The best way to learn how to use this feature effectively is to type VLOOKUP into the help section of Excel, and try the examples. Once you’ve got the hang of it, it should save you lots of time re-entering data.
3 Easy database
Patrick Flannelly, a Part 3 student from the US advises on the best way to create a database which is easy to set up and maintain. ‘Place the cursor in a cell in a spreadsheet where the contents are in the form where each row is a record of data with each column representing a field for the record. Next, from the Excel menu select Data, then List, then Create List, or use the keystroke Ctrl-L. There are some great benefits of using this feature. For a start you can get automatic totals, averages etc, of any column. Select Data, then List, and then Total Row. Use the drop-down box to select which calculation you want in each cell of the total row. You can easily add new records by entering them in the row marked with an asterisk. It’s easy to manage records by using the menu option Data, then Form.’ There are many more useful features you can use on your database by exploring the Data menu.
4 Can’t see it all?
When you’re dealing with large statements it can be very difficult keeping track of what you’re doing when you can’t see the whole thing on your screen. Habib-ur-Rehman, a Part 1 student from Pakistan has a solution. ‘The “camera option” is very useful for large statements, and they can be shown smaller on the page for printing. This option can be shown on the icon bar by following these steps. Click on Tools, select Customize, click on Commands and select Tools in the command window. Now select the “camera” option and drag it to the icon bar.’
5 Need to consolidate?
Olufemi Solomon Sobowale, a Part 1 student from Nigeria finds the consolidate tool particularly useful. This tool allows you to combine worksheets where data are defined by position or by category. ‘By position’ means that the data are in the same position on every worksheet. ‘By category’ means that you want to combine data from tables in which the data may not use a consistent structure. ‘In the workbook that contains worksheets you want to consolidate, choose Data, then Consolidate. Excel displays the Consolidate dialog box. There are many controls in the dialog box, but the primary thing you need to worry about is specifying the ranges to consolidate. You do this by using the Reference box. Specify in the box the first range you want to consolidate. If you are consolidating by position, then the reference should not contain any column labels; if by category, then you should. When you specify the range reference, you click Add, and the reference appears in the All References list. You continue to define reference ranges until they are all complete. If you want the consolidated data to contain links to the original data, then make sure the Create Links to Source Data check box is selected, otherwise clear it. You can then click OK to do the consolidation.’
6 Easy to duplicate
Sometimes when you need to copy sheets, the formatting can be lost. A way to save time and avoid any unwanted changes is offered by Syed Adnan Haseeb, a Part 1 student from the United Arab Emirates. ‘To duplicate the current sheet with links, formulas, and with the formatting intact, hold the Ctrl key down while clicking and holding with your mouse on the current sheet tab. Move your mouse to the right and drop. Exactly the same sheet will be copied without any changes.’
7 Formula for success
Minu Bhimsaria, a Part 3 student from the UK, suggests a way to save time when copying formulas applied in a cell. ‘Instead of copying each time, click on the original cell and drag – not copy – the original cell using the left click button of the mouse. Drag to all the cells that you want to copy the formulae to.’
8 Too complete
Sometimes Excel can cause problems when it automatically completes your cell entries. Muhammad Zain ul Abideen, a Part 2 student from Pakistan, says: ‘To stop automatic completion, you can turn this option off. On the Tools menu, click Options, then on the Edit tab, select or clear the Enable AutoComplete for cell values check box to turn automatic completion of cell entries on or off.’
9 Make the reporting process more efficient
A PivotTable report is an interactive table that you can use to quickly summarise large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest. To create a Pivot Table, go to the Data menu, click on Subtotal, and then Custom. Xuren Tana, a Part 1 student from China, says: ‘Use a PivotTable report when you want to compare related totals, especially when you have a long list of figures to summarise and you want to compare several facts about each figure. PivotTable reports are useful when you want Excel to do the sorting, subtotalling, and totalling for you.’
10 Freezing panes
When inputting data into cells, there is always the problem of inputting the wrong data into the wrong column or rows, especially when scrolling down. Mohamud Mohamed, a Part 1 student from Kenya, says: ‘The problem is overcome by freezing the horizontal or vertical panes, or both, depending on the need. To freeze horizontal panes, you go to the row below it, select the Window menu and then click freeze panes. The row header will be “frozen” (ie appear on top) no matter how many times you scroll downwards. To freeze vertical panes, go to the column on the right and repeat the same procedure. To freeze both vertical and horizontal panes, select the pane below and to the right, choose Window menu and then freeze panes.’
Sometimes you might need to combine characters or numbers that are in different cells. Janet Waweru, a Part 3 student from Kenya uses concatenation to make this task easy. ‘To combine characters or numbers in different cells use: =concatenate(specify the cells to combine). For example, if you have the individual letters of janet in a row of cells you could join them together using =concatenate(A1, B1, C1, D1, E1) in the cell that you want them to be joined together in.’ You can also use the ampersand (&) symbol to join cells, for example =A1 & “ ” & B1 will join together cell A1 and cell B1 with a space (denoted by “ ”) in between.
2 Look it up
‘At work, I recently used VLOOKUP, which I found really useful,’ says Damree Mohammad Allyhim, a Part 3 student from Mauritius. ‘Say you need to fill your income statement and balance sheet in Excel from figures in your trial balance. VLOOKUP can help you to avoid laboriously retyping figures that already exist in your trial balance (extracted from accounting software to Excel). You just need to use VLOOKUP for the accounts code (normally located to the left of your description, eg sales) and the function will automatically insert your figures into the income statement and balance sheet.’
The best way to learn how to use this feature effectively is to type VLOOKUP into the help section of Excel, and try the examples. Once you’ve got the hang of it, it should save you lots of time re-entering data.
3 Easy database
Patrick Flannelly, a Part 3 student from the US advises on the best way to create a database which is easy to set up and maintain. ‘Place the cursor in a cell in a spreadsheet where the contents are in the form where each row is a record of data with each column representing a field for the record. Next, from the Excel menu select Data, then List, then Create List, or use the keystroke Ctrl-L. There are some great benefits of using this feature. For a start you can get automatic totals, averages etc, of any column. Select Data, then List, and then Total Row. Use the drop-down box to select which calculation you want in each cell of the total row. You can easily add new records by entering them in the row marked with an asterisk. It’s easy to manage records by using the menu option Data, then Form.’ There are many more useful features you can use on your database by exploring the Data menu.
4 Can’t see it all?
When you’re dealing with large statements it can be very difficult keeping track of what you’re doing when you can’t see the whole thing on your screen. Habib-ur-Rehman, a Part 1 student from Pakistan has a solution. ‘The “camera option” is very useful for large statements, and they can be shown smaller on the page for printing. This option can be shown on the icon bar by following these steps. Click on Tools, select Customize, click on Commands and select Tools in the command window. Now select the “camera” option and drag it to the icon bar.’
5 Need to consolidate?
Olufemi Solomon Sobowale, a Part 1 student from Nigeria finds the consolidate tool particularly useful. This tool allows you to combine worksheets where data are defined by position or by category. ‘By position’ means that the data are in the same position on every worksheet. ‘By category’ means that you want to combine data from tables in which the data may not use a consistent structure. ‘In the workbook that contains worksheets you want to consolidate, choose Data, then Consolidate. Excel displays the Consolidate dialog box. There are many controls in the dialog box, but the primary thing you need to worry about is specifying the ranges to consolidate. You do this by using the Reference box. Specify in the box the first range you want to consolidate. If you are consolidating by position, then the reference should not contain any column labels; if by category, then you should. When you specify the range reference, you click Add, and the reference appears in the All References list. You continue to define reference ranges until they are all complete. If you want the consolidated data to contain links to the original data, then make sure the Create Links to Source Data check box is selected, otherwise clear it. You can then click OK to do the consolidation.’
6 Easy to duplicate
Sometimes when you need to copy sheets, the formatting can be lost. A way to save time and avoid any unwanted changes is offered by Syed Adnan Haseeb, a Part 1 student from the United Arab Emirates. ‘To duplicate the current sheet with links, formulas, and with the formatting intact, hold the Ctrl key down while clicking and holding with your mouse on the current sheet tab. Move your mouse to the right and drop. Exactly the same sheet will be copied without any changes.’
7 Formula for success
Minu Bhimsaria, a Part 3 student from the UK, suggests a way to save time when copying formulas applied in a cell. ‘Instead of copying each time, click on the original cell and drag – not copy – the original cell using the left click button of the mouse. Drag to all the cells that you want to copy the formulae to.’
8 Too complete
Sometimes Excel can cause problems when it automatically completes your cell entries. Muhammad Zain ul Abideen, a Part 2 student from Pakistan, says: ‘To stop automatic completion, you can turn this option off. On the Tools menu, click Options, then on the Edit tab, select or clear the Enable AutoComplete for cell values check box to turn automatic completion of cell entries on or off.’
9 Make the reporting process more efficient
A PivotTable report is an interactive table that you can use to quickly summarise large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest. To create a Pivot Table, go to the Data menu, click on Subtotal, and then Custom. Xuren Tana, a Part 1 student from China, says: ‘Use a PivotTable report when you want to compare related totals, especially when you have a long list of figures to summarise and you want to compare several facts about each figure. PivotTable reports are useful when you want Excel to do the sorting, subtotalling, and totalling for you.’
10 Freezing panes
When inputting data into cells, there is always the problem of inputting the wrong data into the wrong column or rows, especially when scrolling down. Mohamud Mohamed, a Part 1 student from Kenya, says: ‘The problem is overcome by freezing the horizontal or vertical panes, or both, depending on the need. To freeze horizontal panes, you go to the row below it, select the Window menu and then click freeze panes. The row header will be “frozen” (ie appear on top) no matter how many times you scroll downwards. To freeze vertical panes, go to the column on the right and repeat the same procedure. To freeze both vertical and horizontal panes, select the pane below and to the right, choose Window menu and then freeze panes.’