Shilpa Wymer, Managing Director of Pitman Training centres in London and Manchester has put together 21 top Excel tips that will help increase your productivity and get you noticed.
Today, anybody working in an office will come into contact with spreadsheets and increasingly we are finding that expert knowledge is a prerequisite to many office jobs.
In fact, today’s PAs are increasingly aware that they need to have a thorough understanding of Microsoft Office packages, and Excel is a valuable and sought after tool in the PA’s arsenal.
Even though Excel has been around for a long time, only about 10% of Excel users would be described as experts. On the one hand I find that staggering. On the other hand, from my experience, I can see that most people who come to Pitman Training think they know Excel well, but when assessed, it becomes quite clear that they’ve picked up their knowledge on the job and there are huge holes in their skill set.
The tips below were created in Office 2010 and even though we now have Office 2013 out, the differences are minimal.
Here are the areas I will cover – I’ve made sure I will only look at tips that will save you time and make you more efficient.
1. Customise The Ribbon
2. The Easy Way To Find Your Documents
3. Quick Access Toolbar
4. You Don’t Need A Calculator
5. Changing Enter Key Behaviour
6. Use Keyboard Shortcuts
7. Sharing Workbooks
8. Clipboard Pane
9. Named Ranges
10. Custom Autofill Lists
11. Using The Proper Function
12. Trim & Clean
14. Changing Rows & Columns
16. Conditional Formatting
17. Changing Shape Of Comment Box
18. Formatting Tables
19. Viewing Worksheets Side by Side
20. Save A Chart As A Template
21. Colour & Rename tabs
1. Customise The Ribbon
The team at Microsoft have worked hard to make Office more user friendly and there are quite a few areas where we can begin to customise the programme to make it work harder.
Firstly, let’s start with personalising Excel and really making it work for you. I want you to take a look at your Excel ribbon layout and really think about how you use Excel. For example some of you may be using it to enter data, while others may be required to manipulate data and create charts and pivot tables. Once you’ve thought through the elements you actually need, you can set about customising it. We’ll start with customising the Office Ribbon. I guess by now most of you are getting quite familiar with the Office Ribbon, but just in case here’s how the Ribbon normally looks. It has a number of different tabs which have different tools on.
On these tabs we can add or remove items or even remove a tab completely. To customise the ribbon or any of the tabs you need to select File and Options
This brings up the following box and now you can choose to customise each of the tabs in the Ribbon. Here you will notice we are working on the main tab. Now, if there’s anything you use a lot and you would rather it was on the main tab, add it and obviously if there are things you do not use often then take them off. Don’t be scared if you find out later you do need them as you can always add them back.
Also, if you really want to personalise the toolbar, you can create your very own tab with things you specifically want on it. Just select New Tab at the bottom of the “Customise Ribbon” box and here you can name it whatever you wish. I have named a tab in my own name and it contains my most used functions.
2. The Easy Way To Find Your Documents
If you are used to working on many documents this tip will really help you manage them more effectively. I see many people constantly searching for documents that they use on a regular basis or covering their desktops with hundreds of shortcuts. With Office 2010 there really is no need. I’m going to show you two ways to make searching for documents simpler. The first is that you’ll see here I have 4 documents in the file menu that I have recently been working on. I simply selected the number of recent documents I wanted to show at the bottom of the screen here. You can select as few or as many as you want.
Now, you’ll also notice that all my “recent workbooks” and “recent places” are displayed to the right.
If you regularly access a place or workbook then you can simply pin these here which means that every time you open Excel these places or workbooks will always be pinned at the top of your screen. Let me show you how. First, click on “Recent”. This will bring up your “recent workbooks” and “recent places” you’ve been working on. Next to each of these you will see a picture of a greyed out drawing pin. Simply click on it and it will turn in to a blue drawing pin – now those documents will be there whenever you open Excel.
3. Quick Access Toolbar
The most useful tool that lots of people are still not using to its fullest is the Quick Access Toolbar.You’ll normally find it at the top right of your screen. Just by clicking on the arrow next to it you’ll be able to add general tasks such as “Send as email”, “Create new worksheet”, “Print” and “Save”.
If you want to add other items you can do this by selecting “File” and “Options” and choosing the “Quick Access Toolbar” option. Here you can customise to your heart’s content and have your most used tasks easily to hand.
4. You Don’t Need That Calculator.
Now, this is one that I hope will make you look super-efficient.
As an example, say you manage the sales data for the company and here you have monthly sales figures for each of the sales people. Your manager comes along and says “What was the total amount of sales Lucy did for the first 6 months of the year and what are her average sales for those 6 months?” Here’s where I see many people reach for the calculator. There is no need, you simply select the cells containing this data and then look at the bottom of the screen where you will find the “status bar”. This contains all the data you need. So we can see the sum of these is £133,162.00 and the average per month is £22,194 – really easy eh?
Now if you want to you can also add in other options on the Status bar just by right clicking on it and bringing up the following menu. Here, I can add in Minimum and Maximum to the status bar or customise other elements too.
OK, so maybe you still might need a calculator for some tasks or perhaps you just can’t get used to not using the calculator. If you must use one then here’s my tip. Stick one to your Quick Access toolbar, you’ll find it under “All Commands” when selecting options for customising the Quick Access Toolbar.
Now, whenever you need a calculator you’ll have one easily to hand.
5. Changing Enter Key Behaviour.
If you work on Excel a fair bit you’ll find that when you hit the “enter key” you will automatically be taken to the cell below. However, I sometimes find that I want to enter data across the page rather than just down and find it annoying and hard work to constantly remember to move the cursor to the correct cell. If I want the cursor to move to the right automatically every time I hit enter then I simply need to change the Enter Key behaviour. To do this simply click on File and Options and in Advanced Options and under Editing Options you’ll be able to change the direction to right, up or left.
OK, I think I’ve finally got through the basics of setting up your program and your spreadsheet and getting it working harder for you. Let’s move on to some tips on working smarter with data.
6. Use Keyboard shortcuts
Keyboard Shortcuts are essential to using Excel efficiently, but it’s interesting that many of us don’t use more than the copy and paste keys. These are some essential ones to know.
7. Sharing workbooks
You may sometimes be working on a spreadsheet but may need input or collaboration from others. Sharing a workbook means that you can all work on the same workbook at the same time. Here’s how you do it. Firstly, create a workbook and save to a place on your network. When you are ready to share it simply select the Review tab and select share workbook.
This will bring up a box, like this:
Now put a tick in the “allow changes by more than one user at the same time” box.
There are also some Advanced options here too that you can set should you wish. They allow you to have a little more control by setting:
• Tracking changing options
• When files are updated with changes
• How to resolve conflicts
• Options to include on Personal view
If you need even more control over a shared workbook, you can always ensure that all changes are tracked by selecting “Protect Shared Workbook” on the Review tab and putting a tick in “Sharing with track changes”.
8. Clipboard Pane.
This is incredibly useful when working in Excel as you often need to cut, copy and paste between different worksheets. To activate the clipboard pane simply click on the grey arrow on the bottom right of where you will find the editing options.
This highlighted area is the Clipboard Pane and you can see all the data that I’ve most recently copied and now I can simply click on the one I want to insert.
9. Named ranges
If you are used to creating formulas, this will help make more sense of them and also help you easily locate data that you need to reference.
As a simple example, say you want to show just the sum of Lucy’s sales. Select the data for Lucy and then name it Lucy by simply typing it in the field highlighted.
Now If I wanted to calculate the total sales for Lucy, Ben & Holly. Here’s a really easy way to do it.
Firstly, I select the data for Ben & Holly and save them as a named range as I did for Lucy.
Next, I just do a simple sum for the 3 of them.
I’ve created an equation which references all three as named ranges. In fact when the first letter of each person is typed it will automatically show me the named range. In more complex formulas this can be incredibly useful in helping make sense of the equations.
10. Custom Autofill lists
Most of us are used to Autofill on Excel where if you type in January and hold on to the cell handle and drag – it will automatically complete the other months. But what if you have lists you use all the time in your business i.e. North, South East & West or Low, Medium, High? Well you can create your own Autolists and here’s how. Firstly, select file and options and select Advanced options. Scroll down and you’ll find Edit Customer Lists.
Now, add a New List and either enter the information manually or if you already have the list somewhere you can always import it. So, here as a simple example I added a new list and the entries are North, South, East & West.
The next few tips really are for you if you often find yourself importing data and fiddling with messy data entry issues.
11. Using the Proper function.
Do you ever get data in the wrong format? You’ve just imported some data from another application and it all appears in upper case. Well here’s a nice quick way of changing the data to sentence case, by just using the Proper function. Here’s how you do it.
In column A is the data I have imported; my product list. Next to this is the column I have treated with the proper function and now it’s in the correct format. In the 2nd column you can see that I have simply put in an equation which makes A2 convert to proper text and then I just scroll the formula down.
Now the entries are in sentence case and I simply hide Column A.
12. Trim & Clean
Another annoyance when importing data to Excel is when there are spaces or weird characters. You could of course manually remove each one but this is time consuming. A better way is to use the Trim and Clean Functions.
• TRIM removes all leading and trailing spaces and replaces internal strings of multiple spaces by a single space.
• CLEAN removes all nonprinting characters from a string. These “garbage” characters often appear when you import certain types of data.
Let’s look at how the trim function works. All I’ve done is to use a function to TRIM A2 and scroll this down. You’ll see we have now got rid of the extra spaces. The CLEAN function would work in exactly the same way.
Another tip on working with data that I have found invaluable is the Concatenate function. Strange word I know. Let me explain this one by way of example. I often work with lists of names and when I import them from a database, they sometimes arrive separated in different columns, like this:
I may not want them in this format and need them in one cell. Here’s the easy way to do it.
Firstly, I need a blank column next to the fields I wish to merge. Now, I use the concatenate function as follows:
We are selecting cell A2, placing a comma after it and creating a space between the words by using quotes marks around a space, then we do the same with B2 and C2. Now I just copy the formula down to change the rest of the rows.
14. Change Rows and Columns
Here’s a neat trick when working with data. Sometimes you may want to change the way data is displayed and have the rows changed to columns, so that it makes it easier to review the information.
All you need to do to achieve this is to select the cells in the table, copy them and then when pasting them on to a new sheet select the Transpose option. This will change the rows and columns for you.
SmartArt is a fabulous tool, allowing you to visually communicate information. I think it can be really useful when presenting quite dry information – allowing you to add more depth to figures.
Let me show you how you could use it. In this example, I not only wanted to communicate the sales of the individuals but also the way the department was set up. Here you can see that next to the sales figures I have put the organisational chart for that department.
You too can easily do this by learning how to use SmartArt. First you need to select the insert tab.
Click on SmartArt and then select hierarchy or anything else from the list. Select the style of SmartArt you would like to use and it will insert it into your Excel sheet
Now you just insert the required information into the fields and you have the organisation chart next to the sales figures.
You could also present processes or additional list information this way.
16. Conditional formatting
When working with large numbers or data it can be difficult to see the wood for the trees. Here’s a way that can help you or anyone else who looks at the data to notice anything unusual in the figures or highlight progress. Firstly select the cells you are working on and select Conditional Formatting which you should find on the home tab.
And here’s another example of using the colour scales:
Now, you’ve created a visual image of the numbers. This can really help to identify areas that need to be worked on.
As well as this you can also create and manage rules so you can set the parameters for formatting.
Here you can see it also allows you to set the type and values as well as change colours.
17. Changing shape of comment boxes
Do you often have to put in comments in cells to explain something or as a reminder? Here’s a way of jazzing them up to look better and get your point across.
Most of you will be familiar with entering a comment by right clicking and selecting “Insert Comment”.
Your comment will appear like this when you hover over the red triangle in the corner of that cell. It doesn’t look terribly exciting.
In order to change the shape to something more interesting you need to add the Shapes option to your Quick Access Toolbar. Now, I’m going to show you another way to access all the commands available in the Quick Access Toolbar. Here’s what you do. First, click on the grey arrow next to the toolbar, which will give you all your basic commands. Then click on “More commands”.
Now, by selecting “more commands” you will see a menu you’ve seen before, the Customise Quick Access Toolbar. Make sure you have selected “all commands” at the top, find “change shape” and add it.
Click OK and it will appear on your Quick Access Toolbar.
Next, go back to where you have inserted a note, right click and select Edit note. Click the comments border to select it as a shape. You should now select the change shape option on the Quick Access Toolbar which will give you lots of different shapes you can choose from.
Once you’ve changed the shape you may also want to change colour and text and the final step is to select the shape again and select “Format Comment”.
This will give you various options but here I’m just going to format the text and colour. I’m going to change mine to Calibri Bold 9 and change the colour to this fluorescent green. There are more options but I’m quite happy to keep it simple.
Now, we’ve changed how the note appears and this will help illustrate different comments in a better way – getting them noticed.
18. Formatting Tables
As I said earlier the Microsoft Office team have made it so much easier to perform tasks in many of their applications and if you want to improve the look of your data, here’s a really easy trick.
First select the data you want to format, now select format as table.
You’ll be presented with the following box:
It shows the selected data and I’ve ticked the box to say that my table has headers in.
Click OK and you’ll then be presented with a beautiful table like this.
Now you can change the look and style by just selecting the table, clicking on the format as a table and hovering above the different styles. This will show you how each style looks before you select it.
19. Viewing worksheets side by side
Do you sometimes work on two separate worksheets and wish you could just see both of them side by side – well you can! Here’s how:
Here I have 2 separate workbooks with data I want to inspect them side by side. I simply open both worksheets, select the view tab and click on arrange all.
This now brings up the following box:
Now just decide how you want to view it, either Vertical, Horizontal or Cascade style. I’ll choose vertical.
Select side by side and you’ll see both worksheets side by side, like this.
You can also decide to select “Synchronous Scrolling” which scrolls both worksheets at the same time but if you don’t want this just deselect this at the top.
Or if you want to view the pages horizontally it will look like this:
20. Save a Chart as a Template
Some of you may spend a lot of time creating beautiful charts for presentations and I’ve got a really handy tip for you now.
If you’ve worked really hard at creating a beautiful and clear chart, you may want to use the style as the basis of other charts you create.
This is easy as you can simply create the chart and then save it as a template for future charts. Let’s see how.
First, create your chart.
Now, making sure you have selected the chart, simply go to the design tab where you will find “save as template”, select it and give the chart a name
Whenever I create a new chart I simply find it under insert chart and “My Templates”.
21. Colour & rename tabs
If you work in workbook with lots of worksheets then it’s useful to colour them and rename each one. Here’s one I use all the time:
In order to rename and colour these right click on the worksheet tab and select the “tab colour” option and the colour you want. You’ll also find the rename option here.
That’s it. That’s my last tip. I really hope these tips are useful to you and ones that you will be able to implement.
The amazing thing is that we have covered a lot but haven’t even got on to some of the more advanced and useful things that can be done in Excel like Pivot tables, Macros, Data validation and more. If you’re keen to learn more about these please contact one of our course advisors in Notting Hill, Holborn, Croydon or Manchester, where we’d be happy to help you really master Excel or any other Microsoft courses.