Pro tips for Excel
15 JUNE 11
Love it or hate it, Microsoft Excel is a popular spreadsheet tool that most of us have as part of our Microsoft Office suite. Many of us probably think that we're pretty good at Excel, but it is in fact an incredibly under-utilised tool. So we thought we'd share a few pro tips to take your Excel skills to the next level; to show you a few things that might help in your day-to-day tasks and even smarten up those monthly reports. With better information, you'll be able to make better decisions for your business!
Below we have a few tips from our Excel Pro - John. Take a look for a couple of lessons on something new, and also a few shortcuts to help get things done quicker.
Note: The following tips are applicable for Excel 2007 and 2010
HOW TO CREATE DROP DOWN LISTS
A drop down list can be helpful when you want to create a form or report where you provide the user with a list of selections to choose from.
Step One
Step Two
Now you should see an arrow next to the cell that has your drop down menu.
HOW TO CREATE A BAR GRAPH THAT SHOWS AN AVERAGE LINE
A bar graph with an average line shows how each item is performing against the average of all items. This can be useful when analysing the performance of different departments, regions, stores, etc.
Step One
Step Two
HOW TO PRINT REPEATING TITLE ROWS
This is helpful if your data will print on to multiple pages, but you want to print the title rows (or column headings) on each page.
HOW TO SPLIT TEXT INTO COLUMNS
This can be really helpful if, for example, you've exported data from another program into Excel, but it puts information into one cell, that you need separated e.g. you get the full contact name in one cell, but you want to split it out into to two columns, so that you have First Name and Last Name.
TOP EXCEL SHORTCUTS
Keyboard shortcuts can make your common tasks much quicker. Take a look at the list below to see what might be helpful for you.
Below we have a few tips from our Excel Pro - John. Take a look for a couple of lessons on something new, and also a few shortcuts to help get things done quicker.
Note: The following tips are applicable for Excel 2007 and 2010
HOW TO CREATE DROP DOWN LISTS
A drop down list can be helpful when you want to create a form or report where you provide the user with a list of selections to choose from.
Step One
- Create your data selection by typing your items into cells on the spreadsheet (typically you would put this list somewhere where the user won't see it e.g. on another sheet perhaps).
- Click on the cell that you want to create the drop down list.
- Click on the Data tab, and click on Data Validation.
Step Two
- Click the "Allow" drop-down button and select "List" from the drop down menu.
- Click on the button at the end of the "Source" field.
- Then select your data selection that you created earlier.
- Once you have made your selection, click the button at the end of the field, and you will be taken back to the Data Validation dialog box.
- Click "OK".
Now you should see an arrow next to the cell that has your drop down menu.
HOW TO CREATE A BAR GRAPH THAT SHOWS AN AVERAGE LINE
A bar graph with an average line shows how each item is performing against the average of all items. This can be useful when analysing the performance of different departments, regions, stores, etc.
Step One
- Firstly you must have your list of areas you are comparing and their corresponding performance figures.
- To create the average series, click on the cell and type "=average(and then use your mouse to select the numbers that you would like to graph, then press F4 and then type the close bracket)". Press enter.
- Using F4 makes the formula for the cell fixed so that if you drag down the cell (like we are going to do below) the graph will still be looking at the same data.
- Then, in the cell that has the average for the first item, select the bottom right hand corner of the cell and drag it down, so that you copy the formula to all cells in your list.
Step Two
- Select all of the cells that contain your information.
- Click on the Insert tab, and select 2-D Column.
- Click the bar on the graph series that you want to turn into a line (the average). It will appear selected in the table and the graph.
- Right click and select "Change Series Chart Type".
- Then select "Line" from the Chart Type options. Click OK.
HOW TO PRINT REPEATING TITLE ROWS
This is helpful if your data will print on to multiple pages, but you want to print the title rows (or column headings) on each page.
- Click on the Page Layout tab and select Print Titles.
- Click the button at the end of the "Rows to repeat at top:" field.
- Select your title row(s), and then click the button at the end of the field again.
- This will take you back to the Page Setup dialiog box.
- Click Print Preview if you want to check how this will look.
- Click OK.
HOW TO SPLIT TEXT INTO COLUMNS
This can be really helpful if, for example, you've exported data from another program into Excel, but it puts information into one cell, that you need separated e.g. you get the full contact name in one cell, but you want to split it out into to two columns, so that you have First Name and Last Name.
- Select the data in colum A by selecting cell A1 and pressing Ctrl+Shift+Down Arrow (there's a handy keyboard shortcut for you!)
- Click on the Data tab and click on Text to Columns. In Step 1 of the Wizard, choose Delimited. This means that one specific character (a space) will be used to mark where the field should be broken apart. Click Next.
- In Step 3, choose Space as the Delimiter. Uncheck the other delimiters (such as Tab, which is the usual defualt). Click Next.
- Click Finish. You will find that your data has now been split into two columns.
TOP EXCEL SHORTCUTS
Keyboard shortcuts can make your common tasks much quicker. Take a look at the list below to see what might be helpful for you.
| Ctrl+C | Copy |
| Ctrl+X | Cut |
| Ctrl+V | Paste |
| Ctrl+F | Find |
| Ctrl+S | Save |
| Ctrl+A | Select all cells |
| Ctril+Shift+End | Select from active cell to last cell used in range |
| Ctrl+Shift+Home | Select from active cell to A1 |
| Ctrl+Page Down | Move to the next sheet |
| Ctrl+Page Up | Move to the previous sheet |
| Ctrl+; | Current date |
| Ctrl+: | Current time |
