Category Archives: Microsoft Excel 2013

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

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 – using Icon Sets to show whether figures have gone up, down or stayed the same

In the example above, I would like to use icon sets to show whether sales figures have increased, decreased or stayed static. In cell D5, I created a formula =if(c5>b5,3,if(c5=b5,2,1)) – i.e. if the figure had increased, D5 becomes a … Continue reading

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

Microsoft Excel – Charts with missing values

If you create a line chart where there are blanks in the data, the resulting chart can look rather odd! On the Chart Design tab, click Select Data. From the Select Data Source dialog box, click Hidden and Empty Cells. … Continue reading

Posted in Excel 365, Microsoft Excel, 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