Category Archives: Microsoft Excel 2016

Microsoft Excel – Mac keyboard shortcut for absolute cell references

If you are using a PC or Windows based laptop, you can make an Excel cell reference absolute (or fixed) by pressing the F4 function key on the keyboard after the cell reference. The equivalent if you are using a … Continue reading

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

Microsoft Excel – excluding hidden rows in totals

If you use the AutoSum button in Excel, then figures in hidden cells are still used in the totals. If, however, you first format the data as a table and then do a total within the table, the values in … Continue reading

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

Microsoft Excel – sorting or Charting Week1, Week2 etc

Someone I was training had a spreadsheet with one column showing Week 1 onwards and another column showing sales figures for that week. If you sort the information by another column and then sort by the week column again, you … Continue reading

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

Microsoft Excel – creating a calendar with weekends greyed out

I was demonstrating using the fill handle to create lists of weekdays recently when one of my trainees asked whether we could instead produce a calendar with weekends greyed out. The answer is to use conditional formatting. I first of … Continue reading

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

Microsoft Excel – finding process date using working days from end date

If I have a date in which an order has to be sent and I know the number of working days that it takes me to process the order, I may want to find out when I need to start … Continue reading

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

Microsoft Excel – keyboard shortcuts for moving between worksheet tabs

If you don’t want to click on the tabs at the bottom of each worksheet to move from one to another, the keyboard shortcuts are: Ctrl + Page Down Moves to next worksheet Ctrl + Page Up Moves to previous … Continue reading

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

Microsoft Excel – third working day of every month

Someone asked me recently whether they could produce a series in Excel to find out the third working day of every month. To be honest, it may be as easy to do this manually as you still need to input … Continue reading

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

Microsoft Excel 2013 and 2016 – automatic currency conversion

A client wants to automatically convert £ to $ and £ to €. You used to be able to do this using MSN MoneyCentral straight from within Excel but this now longer seems to be readily available. However, the below … Continue reading

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

Microsoft Excel – conditional formatting on combination of two words

I often demonstrate how to conditionally format based on looking for particular text in a cell, but what if two (or more) words have to be in the text string. In the example below, I want to show cars that … Continue reading

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

Microsoft Excel – finding all birthdays within a particular month

If you have a column containing dates of births, it is easy to find all birthdays falling within a particular month by filtering the data. To do so, either format the dataset as a table – Home tab, Styles group, … Continue reading

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