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

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

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

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.

Click Conditional Formatting.

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.

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.

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.

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.)

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

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.

 

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 will not get the same order as when you started.

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.

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 button.

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

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.

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 all used the Autofill handle to put the days of the week on the top row and the dates on the second row. In this instance, my first column has times of day in it, though it could have anything needed.

Now, I selected the cell range that I wanted to format in this case A1:AE21.

Then I went to Home tab, Styles group, Conditional Formatting – New Rule. From Select a Rule Type I chose Use a formula to determine which cells to format. In the Format values where this formula is true I typed

=A$1=”Saturday”

Then clicked Format. In this instance I went to the Fill tab and chose a shading colour, then clicked OK twice.

I did similarly for Sunday. Probably if I had thought about it for longer I could have combined the two!

What happens though if we don’t have days of the week in our worksheet, just dates? In this case we would have to use the WEEKDAY function to work out the days of the week first. Sunday is weekday 1 and Sunday is weekday 2.

In this case the formulas are

=WEEKDAY(A$1)=1 and =WEEKDAY(A$1)=7

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

Find out more about our Microsoft Excel training Sydney, Australia.

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 processing it.

I can use the WORKDAY function for this. WORKDAY has the syntax:

=WORKDAY(start date, days, [holidays]), – if we want to include holidays we should include these by inputting them somewhere on our worksheet and selecting the relevant range.

Usually this function is used to find a future date; here we need to use it to find an earlier date.

So my formula in cell c2 becomes:

=WORKDAY(a2,-b2)       In this case I am not selecting holidays.

This shows the result as a number – the number of days since 01/01/1900.

On the Home tab, in the Number group, from the dropdown, select Short Date. This then shows the date at which I need to start processing the order. Double-click down to get the rest of the required start dates.

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

Find out more about our Microsoft Excel training Sydney, Australia.

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 worksheet

 

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

Find out more about our Microsoft Excel training Sydney, Australia.