Microsoft Excel – sorting within a pie chart

I was asked by a client whether she could sort her pie chart so the largest number was at the top. You cannot really do this within the pie chart itself but you can do it by sorting within the Excel spreadsheet first.

Here is my pie chart before sorting the data.

Pie Chart1

I then sorted the data from largest to smallest. My pie chart now looks like this.

Pie Chart2

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

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

Microsoft Project – colour coding by Resource Group

I was asked by a client recently as to whether she could colour code by Resource Group.

First add column Resource Group to your view. You can always hide it again afterwards. You may also want to got to Gantt Chart Tools Format tab and get rid of the ticks by Project Summary Task and Summary Tasks for the time being though you will want to put them back afterwards.
Then from the Resource Group filter dropdown, get rid of the tick by Select All and put a tick by the first of your groups. If you want to change text colours of background colours in the entry table, select the filtered rows, and on the Home tab in the Font group, select your required font colour and/or background colour.
If you want to change bar colours, go to the Gantt Chart Tools Format tab and in the Bar Styles group, from the Format dropdown, click Bar, select your required colour.
Go back to the Resource Group dropdown, clear the filter and select your next group and follow the above instructions for colouring each group in turn.
This should work as long as not more than one Resource Group is assigned to any task!
Find out more about our Microsoft Project training in the London area.
Find out more about our Microsoft Project training in Dorset, Wiltshire and Hampshire areas.
Posted in Microsoft Project, Microsoft Project 2010, Microsoft Project 2013, Microsoft Project 2016 | Tagged , , | Leave a comment

Sending a folder by email

A client just contacted me to ask how she could send a whole folder by email.

The answer is that you will need to ‘zip’ the folder.

Right-click on the name of the folder, click Send to, then select Compressed (Zipped) Folder. You can then email the zipped folder.

Find out more about our Microsoft Office training in the London area.

Find out more about our Microsoft Office training in Dorset, Wiltshire, Hampshire.

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

Time Management – Turning off Microsoft Outlook e-mail notifications

ifonlyidknownthat

If you are busy concentrating on writing a report, a proposal, etc, you need to concentrate on the task in hand, not constantly be distracted by an e-mail notification symbol becoming visible at the bottom right of the screen. Believe it or not, most e-mails do not need an instant reply – if we are in meetings, we can usually manage to ignore our e-mails, so why not when we’re busy on a vital piece of work?

Why not turn off your Outlook notifications?

If using Microsoft Outlook 2007 or earlier versions:

Click Tools – Options. The Options dialog box will be displayed.

On the Preferences tab, click E-mail Options, then click Advanced E-mail Options. The Advanced E-mail Options dialog box will be displayed.

In the When new items arrive in my Inbox section, remove the ticks from Play a sound, Briefly change the mouse cursor

View original post 187 more words

Posted in Uncategorized | Leave a comment

Microsoft Access – finding duplicate records

ifonlyidknownthat

If you want to find duplicate entries in a field in a table or query, the easiest way to do this is to use the Find Duplicates Query Wizard. For example, you may wish to find records with the same name but different addresses, or as in the example below, which suppliers are in the same city.

To use the Find Duplicates Query Wizard

If using Microsoft Access 2007 or Microsoft Access 2010, click the Create tab, and then in the Other group, click Query Wizard. If using Microsoft Access 2013 or Microsoft Access 2016, click the Create tab, then in the Queries group, click QueryWizard. If using Microsoft Access 2003 or earlier, from the Database window, click the Queries object button, then click New.  In the New Query dialog box, select Find Duplicates Query Wizard, then click OK.

The New Query dialog box will be…

View original post 96 more words

Posted in Uncategorized | Leave a comment

Microsoft Excel – #### in a cell

ifonlyidknownthat

You may have had the experience of think you are doing something wrong, because you see an Excel cell filled with hashes. In fact this is not an error at all, but instead means that your column is not wide enough to display all the data in the cell.

So, if this happens:

Navigate to the first cell where the problem occurs.

Move the mouse to the top of the column, and place it over the right-of-column marker, where it changes shape to a double-headed arrow. Double-clicking the column marker using the left mouse button automatically resizes the column to fit its widest entry. The hashed-out entries should now display numbers correctly.

There are two other ways to adjust column widths.

First, you can manually resize a column. Hover the mouse pointer at the right-of-column marker, where it changes shape to a double arrow. You can then click and drag with…

View original post 192 more words

Posted in Uncategorized | Leave a comment

Microsoft Outlook – greying out previous appointments

I was asked recently whether there is a way to show past appointments in Microsoft Outlook in a different colour to future appointments.

From within the Calendar, go to the View tab, then in the Current View group, click View Settings.

Calendar format1

Click Conditional Formatting.

Calendar Format2

Click Add. Type in a name for the rule in the Name box. From the Color dropdown, select the required colour, then click Condition. Click the Advanced tab.

From the Field dropdown, select the End field. From the Condition dropdown, select on or before. In the Value box, type Yesterday.

Calendar format3

Click OK three times.

Find out more about our Microsoft Outlook training in the London area.

Find out more about our Microsoft Outlook training Dorset, Wiltshire and Hampshire.

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

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 Mac is to press ⌘T.

Find further details of our Microsoft Excel training in the London area.

Find further details of our Microsoft Excel training in Dorset, Hampshire and Wiltshire.

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 hidden cells are not included.

To format data as a table, click in any cell in the dataset, then press Ctrl + T or on the Home tab, in the Styles group, click Format as Table and select your required table style.

The data will be formatted as a table and the Table Tools Design contextual tab will be displayed. Check the Total box and in the Totals  row, select the column or columns that you want to total (you can also choose Average, Count etc.)

Table2

Now, say I hide rows, 6 to 9, the Totals row will change to reflect only data that is displayed.

Table3

Find out more about our Microsoft Excel training in the London, Middlesex, Surrey areas.

Find out more about our Microsoft Excel training in the Dorset, Hampshire, Wiltshire areas.

 

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.

Week1

If you sort the information by another column and then sort by the week column again, you will not get the same order as when you started.

Week2

This is because the Week field is a text field so Week 10, etc come before Week 2. This is not usually what you want if you need to report on the data, chart the data, etc. So before you do any manipulation of the data, you could create a custom sort.

To do this, select the data in the Week field.

Go to File – Options – Advanced. Scroll down to the General section, then click Edit Custom Lists.

Week4

Check that the required list is in the Import list from cells box.  Click Import. Click OK twice.

Now if you sort by another column and want to get back to the original sort order, from the Data tab, click the larger Sort Week5button.

Select Week from the Sort by dropdown, then from the Order dropdown, click Custom List.

Week6

Week7

Select the relevant list, then click OK. You will then have your required sort order.

Find out more about our Microsoft Excel training in the London area.

Find out more about our Microsoft Excel training in Dorset, Somerset, Wiltshire, Hampshire.

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