Explore in-depth tutorials and strategies for data management, analysis, and visualisation using Microsoft Excel.
When I add financial data to my worksheets, I generally type in the content and format the cells afterwards – here are two ways to format cells with currency. I select my active cells, then I then click on all the cells, right click on format cells, and then go through the dialogue box to […]
If you’ve ever found yourself repeating the same tasks in Excel—copying and pasting data, formatting sheets, or generating reports—you’ve probably wondered if there’s a faster way. That’s where Excel VBA comes in. It’s a powerful tool that lets you automate routine tasks and streamline your workflow. Whether you’re already exploring data tools like Power BI […]
When I made the switch from Excel 2003 to Excel 2010, the Ribbon confused me and it still takes me a while to figure out which tab holds the functions I need. When I was on the intermediate Excel course the other week, I did notice that the trainer was talking about the buttons in […]
This may seem a back-to-basics question, but it a useful one, at any stage of Excel. This is something I’ve learned from attending our courses – it is always good to ask “What else can I do with Excel?”, “Can I automate that function?”, and “Can Excel do that?” Excel is a spreadsheet and a […]
I was on an Excel Intermediate course last week, and I noticed that when I was working with a chart, specific tools appeared. When I clicked out of my chart, the tools would disappear…What is going on Excel? Why are you doing that? How do I get them back? (Mild panic followed…”have I broken it?”) […]
It can be a pain when you have a list of names that are all in lower case, and you want to be able to change them to the right case. This can be easily completed using a formula to make the changes. To change this list of names to the proper case, I can […]
The previous post showed how to add a drop down list using a short list of terms. Entering a drop down list can be even quicker when applying yes/no choices to Excel. To set up the drop down list, I select the cells where I want the drop down lists to appear – in this […]
Anything that can cut down the time it takes to add data and improve accuracy is good in my book. Creating a drop down list isn’t as tough as I thought, so here is how to set up a drop down list using Data Validation. I have a set of customers, that come under four […]
I love shortcuts, they save me loads of time by using a couple of keystrokes instead of going to different tabs and menus. Here are my 10 favourite Excel shortcuts to speed up data entry in Excel. To undo the last action – use Control + Z no matter what program you are using, this shortcut […]
You can save yourself time scrolling across your Excel documents by wrapping text in column headings. It’s easy to do, takes a few seconds and improves the way you can move through and use your Excel worksheet. Here is a worksheet where better use of column format could help. I have used autofit to make […]
Worksheets that you don’t want other people to see or that you don’t want to delete but just don’t want to be visible, can be hidden. To hide a worksheet… Right click on the worksheet tab of the worksheet you want to hide Select Hide from the list Straight away the worksheet will disappear. Should […]
If you need to make a copy of a worksheet, rather than copying and pasting you could… Right click on the worksheet tab Select Move or Copy from the list and a dialogue box will appear. Select where you want the sheet to be positioned Click in the check box beside Create a copy Click […]
If you are printing a worksheet that will be more than one page, you may have a little difficulty understanding data on certain pages because the column and/or row headings are not visible. Should you wish to see the column / row headings on every printed out page… On the Page Layout ribbon, in the […]
To give your worksheet tabs different colours… Right click on the worksheet tab Select Tab colour from the list Click on the colour of your choice
If you are printing a worksheet that will be more than one page, you may have a little difficulty understanding data on certain pages because the column and/or row headings are not visible. Should you wish to see the column / row headings on every printed out page… On the Page Layout ribbon, in the […]
Sparklines are small charts that fit into a cell in your Excel workbooks. They act as a great visual summary of trends that use a small amount of space and allow you to make quick and effective comparisons in the blink of an eye. Features Sparklines will actively update as you add new data. Sparklines […]
Easily copy & paste using the Transpose option This is a very useful little tool (and one of many that we cover on our Advanced Microsoft Excel courses). Sometimes when setting up a worksheet you may realise that it would be better to put your column data into a row. One way to achieve this […]
Adding comments to an Excel spreadsheet helps when you are sharing a worksheet with colleagues. You can add extra information to a figure in a cell, use to note trends, or add an action note. The comments are hidden when you are working in your normal worksheet view, but you know a comment has been […]
One problem with sharing workbooks is accidental changes to formulas – a small change can negatively effect a whole document. How to hide a formula and stop unwanted changes Right click on the cell that contains the formula. Go to the Context menu and select Format Cells. From here, click on Protection, and choose the Hidden […]
It’s easy to fall into a habit, and manually type in a formula and copy and paste it within a worksheet. If so, you may not be getting the most out of the Formula tab on the Ribbon. From here, you can save yourself time by using Excel’s ready made formulas using Functions. The Formulas tab has […]
Dates are key pieces of information in Excel spreadsheets used for everything from planning, assessing progress and analysing results. Sometimes you may find that when you use dates in formulas, or as part of sorting or filtering data, the results don’t come out in the way you’d expect. This can be frustrating and slow down […]
I like to have easy access to web pages that link to the data I’m working on. I can copy and paste the web address into a column, which is fine, but takes up lots of space. A colleague showed me a good way to include my hyperlinks, where they are easily accessible within the […]
What is the difference between Autofilter and Sort in Excel? Why use one and not the other? I found out the difference when I was trying to sort through an enormous worksheet and a colleague asked me why I wasn’t using the Autofilter. I didn’t really want to admit that I didn’t know what it […]
There is more than one way to enter data into an Excel worksheet. Sometimes we stick to typing directly into cells, but there are different ways to enter data which can speed up your data entry work. Type directly into a cell and add your data. You know a cell is active as it is highlighted […]
Autofill is rapidly becoming a new trick for me today. I’ve used it to add weekdays, months of the year, and numbering columns. But there is more to it than that and Excel offers a range of Autofill options. To get the full list of options, you need to find the Auto Fill Options button. In […]
I was on an Excel intermediate course yesterday and here is a tip I learned that can make data entry faster in Excel. Autofill covers anything from dates, days of the week, to months of the year and more…Excel will automatically fill in the series. this can save so much time and reduce data entry errors. For […]
When you set up or amend a worksheet in Excel, it can speed up your work if you copy the formats you like. The Format Painter is there to help you do just that. It is located in the Home tab and you can use it to copy formats from one cell to another (or […]
One of the best tips for Excel that I’ve ever been given is “if you can automate it…set up a formula”. There is nothing more frustrating than trying to figure out calculations if you don’t understand the rules – Excel can do the number crunching, which means I can focus on adding the data and […]
Using different views in Excel can help speed up the quick analysis of data. Using Views is a great tool for viewing and organising large spreadsheets. Zoom in on current selection I use Excel for 90% of my work and using views is an effective way to zoom in and out on data. To access […]
You can learn how to get more out of your data with Excel 2010 training courses London. For example, Autosum offers you so much more than just, well, auto-summing. It belongs in the Formulas tab, but you’ll also find it tucked away on the Home tab. Tip: I use Autosum so much I like to […]
I can filter data in my Excel worksheet, using the number filters. This is another time-saving feature that helps me to get the data I need, quickly and easily. You can access the number filters, by selecting the down arrow next to the filter in the column you want to review. The filters offer you […]
An experiment using conditional formatting… I have a list of fictitious students who have taken an Excel exam this morning. The pass mark was 55, but I want to help the whole group to achieve a Grade B with a pass mark of over 70%. I want to be able to find who needs to […]
Here is a quick tip for customising your Excel worksheet. I like my Excel worksheets to have alternate shading or colours so that it is easier for me to read across rows. Now, I could set these up manually, by shading in my rows, but if I use sort, or add new rows, the shading […]
You may have come across the situation where you have a list of names in Excel, with each name in a single cell and you want to separate out the first name and surname into separate cells. You can do this with the Text to Columns feature but this has limitations. Alternatively you can do […]
If you’ve used Excel for some time, you may want to learn more about the different logical functions such as IF, AND and OR. This post describes how these functions can be combined to perform very useful composite logical operations. IF Function The IF function will carry out one of two actions. One action is […]
The convert function is used to change data from one unit type to another in Excel. It’s often used to convert dates, but it can do so much more as there is a range of conversion units that you can use to make your life easier. The convert function is an engineering function. In Excel […]
This is a quick function to try out and can add a bit of fun to a worksheet. I’m creating a worksheet listing different forms of marine life for a quiz for some children visiting the local aquarium. It will make the screen look more inviting if I have a photo behind the worksheet to […]
This is quick to do and a useful way to add financial information from Excel to a Word document. This is an effective way to add a snapshot of financial data without the need for Excel’s tools. Tip: The Excel data will appear as a table in Word. It will show the figures but you […]
One of our trainers showed me this function when I was trying to find data in a huge worksheet. It is a fantastic function for List Management and saves me lots of time and stressing. Here is a simple example to demonstrate how to use filters in Excel. I have my worksheet with details of […]
If you are planning a budget for a trip involving two different currencies, fluctuations in conversion rates can create more work for you. The solution is to use a formula that automatically updates all the results if the conversion rate changes. At the top of my worksheet, I have added a currency conversion rate. I […]
In one of my earlier blog posts, I looked at how an Excel formula can add working days to a date. This was for a manager who wanted to set up deadlines for the tasks to be completed within the team’s appraisal meetings. I want to build on this and find out how to add […]
I use Excel for planning work and setting deadlines and I always work better if I know the due date for a piece of work. I used to manually work out the number of working days between dates, using my Outlook calendar, and then switching between screens to then add the dates to my Excel […]
Text wrapping allows you to make all the contents of a cell visible without adjusting the cell’s size. When you enter data, if that data is longer than the width of the cell, it appears in one of two ways: (1) If the cell to the right contains data, you will see only as much […]
I’ve recently upgraded from Excel 2003 to 2010 – and I’m scared. What if it’s too tricky, what if I break it…. and then I thought I could do save time by teaching myself… So I’ve made my list to overcome this fear…and I’m sharing it because I suspect that I’m not alone… I use […]
I use timings in worksheets alongside other key data, for example, in a project sheet, I have lists of individual tasks, with time started, time ended, total time taken, and then cost based on time taken multiplied by charge etc. So when I want to calculate a mixture of times multiplied by costs, I get an […]
Auto fill is a feature of Excel that automates the input of certain data such as days of the week, months of the year and quarters. It’s very simple to do and is a great time saver. If for example you needed to have days of the week on a spreadsheet, you only have to […]
I’m new to the charting side of things…I’m used to staring at numbers on worksheets and trying to figure out what it all means…and admiring those around me who use charts…so mysterious…so professional looking…so much nicer than my rows of figures. Charts make it so much easier to spot trends and analyse data – and […]
For those of us who can break out into a cold sweat at the mere mention of a “formula”, Excel has functions already set up to remove the need for towels and darkened rooms. I use Excel’s common functions every day – autosum, averages, count and minimum and maximum – all I have to do […]
I love time-saving tips and templates are a great way of setting up new worksheets without the hard work of formatting, setting up formulas and trying to make things look good. What I really want to do, is on get on with “the important stuff” of entering the data quickly, so that I can analyse […]
Today I wanted to be able to show the text which was stored in another cell, within a ‘string’ of text. I wanted to have a name ‘John’ as plain text in cell C3. In cell A1, I wanted to be able to display ‘Hello <whatever is in cell C3>’. My colleague Doug here at […]