Microsoft Excel – is my date formatted as a date?

Sometimes when we are working with dates in Excel, things don’t work out quite how we expect usually because what we think is formatted as a date isn’t actually so!

If you look at the formulas by pressing Ctrl + that funny key to the left to the number 1 which has three symbols including the French grave symbol, or by going to the Formulas tab and in the Formula Auditing group, click Show Formulas, any dates will be shown by their values whilst any that are not dates will still be shown as dates.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Excel – repeating x rows on each page of printout

I was training someone today and she wanted to be able to print the first three rows of her spreadsheet on each page of printout – there were 4 pages in total.

To do this, go to the Page Layout tab, then in the Page Setup group, click Print Titles. The Page Setup dialog box will be displayed with the Sheet tab uppermost.

Click in the box to the right of Rows to repeat at top.

Now with your mouse go to the row numbers and drag down the required rows that you want to see at the top of each printed page. Alternatively, you can type the row numbers in, e.g. 1:3.

Click OK. Now when you print, you will see the selected rows on the top of each page of printout.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Excel – keyboard shortcut for scrolling from worksheet tab to worksheet tab

I was training Excel via Zoom yesterday and for some reason, because of her screen setup, one participant could not see the worksheet tabs on her screen. Nor could she right-click towards the button left to find the Activate dialog box to move the worksheet we needed to use.

To move to the next worksheet, the relevant shortcut keys are Ctrl + Page Down. To move to the previous worksheet, the relevant shortcut keys are Ctrl + Page Up.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Excel – Geography

If you are using Excel 365 on the Data tab, in the Data Types group, you will find one called Geography. You may have wondered what it is for. If you have names of cities, etc and want to know sales figures per thousand population or other such statistics, this feature can be useful.

Select the required cities, then click the Geography icon. A list of possible fields will be displayed. These include:

• Admin Division 1 (State/province/other)
• Admin Division 2 (County/district/other)
• Area
• Country/Region
• Image
• Latitude
• Leader(s)
• Longitude
• Name
• Population
• Time Zone(s)

In this case, I want to find the population of each city, so I click on that on the list. The populations are recorded next to the city. I could have looked up each one in turn online but this way is rather quicker.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Excel – using conditional formatting to compare figures across two different worksheets

If you have two worksheets with similar data, you may want to use conditional formatting to show whether the information in one sheet is higher than the corresponding data in the second sheet, for example you may have Sales figures for two different regions and you want to see which are higher, or you may have figures where two months or two years’ worth of data are on separate worksheets and would like to compare them visually.

In the example here, I want to compare sales figures in the north with sales figures in the south.

On the worksheet showing the figures for the north, I select cells B3:H3.

On the Home tab, in the Styles group, click the Conditional Formatting dropdown, click New Rule. Click Use a formula to determine which cells to format.

In the Format values where this formula is true box, type

=B3>South!B3

Click Format and select the formatting of your choice. Click OK twice. Those selected cells in the first worksheet where the numbers are higher than the corresponding figures in the second sheet will be conditionally formatted.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Somerset, Dorset and Hampshire.

Microsoft Excel – why doesn’t Insert Timeline work with my pivot table?

A useful tool if you have created an Excel pivot table is the Timeline which allows you to filter your pivot table to see figures for a specific time frame.

Creating a timeline involves having at least one date field in your data set, otherwise you get an error message.

Sometimes the raw data looks to have date fields but when you look at them more closely, they are not actually formatted as dates. If you click in a cell in the Date column, on the Home tab, in the Number group, check that the field is formatted as Date. If not, you need to convert it and then go back to your Pivot Table and click Refresh All. You should then be able to use the timeline.

One major annoyance is when the data source is being pulled in from another source on a regular basis and the field needs to be reformatted each time. In this case if you don’t convert the field before refreshing the data, the Timeline will go blank.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Somerset, Dorset and Hampshire.

Word 365 – where has my spellcheck button gone?

In previous versions of Microsoft Word, the Spellcheck button used to be on the Review tab and I would often encourage people to add it to their Quick Access Toolbar so they could find it more easily.

Spellcheck has now been replaced by Editor . It is found both on the Review tab and also on the Home tab, which makes finding it easier. Clicking on the Editor icon brings an Editor pane to the right of the window

.

To run the spell check, click Spelling. before you even click on it, it shows how many spelling mistakes it thinks you have made.

On clicking Spelling, your first spelling mistake is displayed with suggestions as to what the spelling should be.

Click on a suggestion to replace your current spelling or click Ignore Once, Ignore All or Add to Dictionary. The Spellcheck then moves on to your next perceived spelling mistake.

The F7 function key still also works for spellcheck – it also opens up the Editor pane.

Find out more about our Microsoft Word Training in the London area or our Microsoft Word training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft 365 – I’ve lost my Quick Access Toolbar

As those of you who are Microsoft 365 users will know, Microsoft are continually making changes to it!

Recently, my Quick Access Toolbar has no longer been showing at the top left of my window.

To get it back, from the application you are using, click File – Options, then at the left click Quick Access Toolbar.

Towards the bottom of the dialog box, check the Show Quick Access Toolbar. From the Toolbar Position dropdown, select whether you want the toolbar to be shown Above Ribbon or Below Ribbon. Click OK. Your Quick Access Toolbar will now be visible.

Find out more information about our Microsoft training in the London area or our Microsoft training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Outlook – dictating an email

Did you know that you can now dictate an email if you want a change from typing? This can be a very handy feature for those of you still working from home and want to give your fingers a rest.

When you start composing your email, on the Message tab, in the Voice group, click Dictate. You can then start talking and Outlook will translate what you are saying into the written word. It will recognise punctuation that you say such as comma, full stop, new paragraph etc.

A box will display at the bottom of the screen, clicking on the microphone icon will pause the dictation until you click on it again.

Why not give it a go? It may type a few words wrong but it is not bad.

Find out more about our Microsoft Outlook training in the London area or our Microsoft Outlook training in Wiltshire, Dorset, Somerset and Hampshire.

ONEDRIVE – FINDING ALL FILES SHARED TO ONE PERSON

Although this seems an easy thing to do it is not. Files which have been shared can be found easily but not a list of files shared to one person. The file/files need to be tagged with the person’s name and the search made on the tag. Tags are also known as keywords.
When you share a file/folder in OneDrive you can view that the file is shared
via:

File Explorer
An icon appears in the Status column designating that the file/folder is shared.
There is no indication who the file is shared with.

OneDrive Online
A note appears in the Sharing column indicating that the file / folder is shared.
Right clicking the file/folder gives you a Manage Access command which displays a Manage Access pane to the right of the screen. Which gives a list of all the people who have access to the file.

Finding all shared files

OneDrive Online

To the left of the screen is a Shared option.

Click here.

Two tabs appear at the top of the screen: Shared with you to view files someone else has shared with you; and Shared by you to view the files you have shared with other people.

Viewing one person’s files you have shared

There seems to be no way to filter, sort or just view all the files you have shared with one particular person.

What can you do?

The shared files can be tagged with the sharer’s name. This would need to be done manually in File Explorer whilst viewing the files in OneDrive. More than one file can be selected at a time by clicking on the first file and then holding down Ctrl and clicking on the other relevant files.

Tag a File or Files

In File Explorer go to OneDrive and select the file or files.

On the View Ribbon click on Details Pane to the left of the ribbon.

The Details pane appears to the right of the screen.

Click into the Tags area – Add a tag (if a name is already there click into the area and the Add a tag command appears).

Type the name or names. If you have used the name before it should appear below where you
are typing, click into the tick box to add.

Click Save at the bottom of the Details pane.

NOTE: If you get an error message close File Explorer and try again a few minutes later.

Search for a Tag

To find all the files tagged with one person’s name:

In File Explorer select the folder to search.

Click into the Search box.

Type tags: the name you want to search for and press Enter.

All the files tagged to that person appear in the list below.

The Search Ribbon also appears once you have typed something into the Search box.

Click the X Close Search command.

The Search Ribbon – Other properties drop down list gives you the option to choose Tag once the ribbon displays but it does not display until should you search for something.

NOTE: Check that the All subfolders option is selected if there are sub folders within the folder you are searching.

Add the Tag column to File Explorer

In File Explorer go to the folder to find the tag.

Ensure the Details layout is on display.

Right click an existing column heading.

Click on Tags and a Tag column will be added to the details area.

The names of the people tagged in the file appear and the down filter arrow allows you to filter for individual people.

NOTE: the problem is that the Tags column is only added to the folder you are in and would need to be added separately to all folders.

Find out more about our Microsoft Office training in the London area or our Microsoft Office training in Wiltshire, Dorset, Somerset and Hampshire.