Category Archives: Microsoft Excel 2016

Microsoft Excel – VLOOKUP giving n/a though looks like cells match

A client recently asked me to look at their Excel workbook where a VLOOKUP was showing N/A all down the column. I could see that some of the cell references did indeed appear to be there in the table array … Continue reading

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

Microsoft Excel – using conditional formatting to highlight upcoming renewal dates

A client has employees who have to renew various work certificates every 3 years. If you have a lot of employees it would help to see when renewal dates were approaching. This can be done by using Conditional Formatting in conjunction … Continue reading

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

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 – showing field names as headings rather than “Row labels” in Pivot tables by default

For many versions of Microsoft Excel I found it annoying that it said Row labels and Column labels in a Pivot Table instead of the actual field headings. In Excel 365 this can now eventually be changed to show field … Continue reading

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

Microsoft Excel – Using Quick Analysis for % of Totals and Cumulative Totals

To calculate % of Totals in Excel, you would normally have to use absolute cell references or name the Total cell. Similarly for cumulative totals, you would have to create the relevant formula. Provided you have Excel 2013 or a … Continue reading

Posted in Excel 365, Microsoft Excel, 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 Office – creating chart showing only weekdays

I have some Excel data that only shows data for week days as I do not work weekends. However, when I came to chart it, it put the weekend dates in, showing blanks. This wasn’t what I wanted. Right-click on … Continue reading

Posted in Excel 365, Microsoft Excel, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Microsoft Office 2013, Microsoft Office 2016, Microsoft Office 365, Microsoft PowerPoint 2013, Microsoft PowerPoint 2016, Microsoft Word 2013, Microsoft Word 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