Category Archives: Microsoft Excel 2007

Microsoft Excel – VLOOKUP showing formula rather than result

I received a workbook from a client the other day. The first thing I usually do when I receive someone else’s workbook is to display the formulas to quickly see what is raw data and what are formulas. (To do … Continue reading

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment

Microsoft Excel – Calculating the difference between times

If you are collecting data in terms of dates and hours and minutes, you may well want to find a time difference between two columns, e.g. time needed to service calls, time taken for order to be dispatched, etc. First … Continue reading

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Uncategorized | Tagged , | Leave a comment

Microsoft Excel – Adding data across worksheets where labels are not all in the same place

If you have data in several worksheets which you want to add together and the labels are not all in the same place, you can do so using the Consolidate feature on the Data tab . Suppose I have two … Continue reading

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Excel – due date end month following invoice date

I was doing some online virtual Excel training last week and the client asked how to work out the due date on invoices if it is the end of the following month after the invoice date. There is an Excel … Continue reading

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Excel – arranging countries into regions in a PivotTable

If you are a global company and are analysing sales by region, even if the name of the region isn’t in the raw data, you can group countries together by region as long as you know the appropriate regions to … Continue reading

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment

Microsoft Excel – freezing top row in a Pivot Table

I was at client site recently and someone had a Pivot table with a lot of rows. It was therefore hard to see what the column headings were. Just as when you are trying to freeze rows in any Excel … Continue reading

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Excel – working out school year from date of birth

School years run from September to August, so in general it depends on both the month that a child is born in as well as the year. So if you have a list of dates of births and need to … Continue reading

Posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Excel – conditionally formatting a whole column range if any cell in that column is blank

This is a question that we were posed at training recently. First select the data area to be formatted. In the example here, I have selected B2:D7. Then on the Home tab, in the Styles group, from the Conditional Formatting … Continue reading

Posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Excel – using icon sets to compare two sets of figures

If you are looking at data over time, you often want to see visually whether numbers have gone up, down or remained static. Create a third column to show the difference between the two sets of data, in this case … Continue reading

Posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Excel – quickly unhide all columns or rows

If you have hidden multiple rows or columns in Excel, it can take a while to work out what you have hidden to unhide again. It is also hard to work out how to unhide column A or row 1! … Continue reading

Posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Leave a comment