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.

Adding text to a formula in Microsoft Excel

ifonlyidknownthat

You want to have text and the result of a formula in the same cell; for example the cell should say “Total Sales Made: ” followed by the result of the calculation to work out annual sales.

Starting Point: Click in the cell in which you want the result.

Script:

Type an equals sign as all formulae start with an equal sign, then type in the required text enclosed within double apostrophes, in this case: “Total Sales Made: “ Now type in an ampersand (&) sign. Now enter the relevant formula, in this case SUM(B4:B25).

Press the Enter key.

Format the cell as required.

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

Find out more about our Excel training Sydney, Australia.

View original post

Microsoft Excel – Telephone numbers

ifonlyidknownthat

A lady who I was training had a column of telephone numbers, some of which had the 0 in front of them, while some didn’t. She wanted all the numbers to be correct with a 0 at the front. First of all we formatted the column as text.

Assuming the first phone number was in cell B2, the relevant formula, probably in cell C2, is

=IF(LEFT(B2,1)<>”0″, “0”&B2,B2)

Use the Autofill handle to copy this down for the rest of the phone numbers.

Then select the now correct numbers, copy them, click back in cell B2 and paste values. You now have a nicely formatted column of phone numbers and can delete the figures in the column where you wrote your IF statement.

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

Find out more about our Excel training Sydney

View original post 2 more words

Ghost verbs

ifonlyidknownthat

Ghost verbs are vague, distracting and cut the life from living verbs.

“Discussion” is a ghostly form of the verb “discuss”.

So, if I said: “We will hold a discussion on it soon”, you’d have to do some thinking.

Why? Because “hold” means nothing, so you flip to “discussion” and change it to the living verb “discuss” for sentence meaning. What a waste of time!

“We will discuss it soon” is what I should have said.

That is the most efficient way: fewer words and quicker meaning.

Our business writing expert, Ron Denholm, examines 20 ghost verbs here:

http://www.slideshare.net/RonaldDenholm/ghost-verbs

Learn more about our Business Writing training courses Sydney, Australia.

Learn more about our Business Writing training courses London, UK.

View original post