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

Posted in Uncategorized | Leave a comment

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

Posted in Uncategorized | Leave a comment

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

Posted in Uncategorized | Leave a comment

Microsoft Word – using the CreateDate field

ifonlyidknownthat

If you are setting up a letterhead template you may want to insert the date, so that when the template is used, it inserts today’s date into the document. However, once you have created your letter and saved the document, you no longer want the date to change if you re-open the letter at a later date.

Start your template as required. When you get to the date, click the Insert tab, then in the Text group, from the Quick Parts dropdown, click Field. The Field dialog box will be displayed.

From the Field names box, click CreateDate.

In the Field properties section, click your preferred date format. Click OK.

Type in any other required information into you template. Click File – Save As. From the Save as type dropdown, select Word Template. Give an appropriate name to your template, then click Save. Close…

View original post 79 more words

Posted in Uncategorized | Leave a comment

Microsoft Word – getting a blank row above a table

ifonlyidknownthat

On occasions you may have started a table at the beginning of a Word document and it then seems impossible to type directly above the table. If this happens to you, click within the top row of the table, then from the Table Tools Layout contextual tab, in the Merge group, click Split Table. This will move your table down from the top of the document and you are able to type above it.

Find more information about our Microsoft Word training Sydney, Australia.

Find more information about our Microsoft Word training London, England, or our Microsoft Word training Hampshire, Wiltshire, Dorset.

View original post

Posted in Uncategorized | Leave a comment

Outlook e-mail search options – current folder as default rather than current mailbox

Above your email messages, there is a Search box.

search1

By default, if you are in the Inbox, it will say Search Current Mailbox, if you have another folder selected, it will search that folder. To change settings so that it will just search the current folder no matter what e-mail folder you are in, click File – Options – Search.

search2

In the Results section, select Current folder instead of Current folder, Current mailbox when searching from the inbox.

Click OK.

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

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

Keyboard shortcut for Snipping Tool

I was doing Windows 10 and Office 2016 training earlier this week and was asked about setting up a keyboard shortcut for starting the Snipping Tool.

To set up a keyboard shortcut for the Snipping Tool, search for the Snipping Tool, then right-click, click More, click Open File Location. The Windows Accessories box will open. Click the Shortcut Tools Manage tab. Right-click on the Snipping Tool, then click Properties. The Properties dialog box will open.

snipping-tool

Type you required keyboard shortcut in the Shortcut key box, then click OK.

You can then use your chosen shortcut keys to open the Snipping Tool.

Find out more information about our Microsoft Office 2016 upgrade workshops and training in the London area or our Microsoft Office upgrade workshops and training in the Dorset, Wiltshire and Hampshire areas.

Posted in Uncategorized, Windows 10 | Tagged , | Leave a comment

Microsoft Excel – splitting currency symbol from amount

ifonlyidknownthat

A client had a spreadsheet in which he had a column with monetary values in the form EUR2000, AUD234, USD55555, etc. He wanted with split into two columns with the currency symbol in one column and the amount in another. Luckily for me, practically all his symbols were composed of three letters.

If the column with the monetary values in isn’t the last column in the worksheet, first insert an extra column to the right. Then select all the monetary values, go to the Data tab and in the Data Tools group, select Text to Columns. The first Convert
Text to Columns
Wizard box will be displayed.

Click Fixed Width, then click Next.

Click after the third character to create the relevant break, then click Next.

If required, make changes in Step 3, then click Finish. You will then have one column with currency types…

View original post 127 more words

Posted in Uncategorized | Leave a comment

Making an Outlook e-mail into a task attachment

ifonlyidknownthat

We have previously looked at transforming an Outlook e-mail into a task in our To-Do list and then making that into a Calendar appointment as good time management. When we do that the e-mail is still there as well as being a task.

If we no longer need to keep the e-mail, we can instead move it into our Task folder as an attachment. To do so, right-click on the e-mail in the Inbox, click Move to Folder. The Move Items dialog box will be displayed. Click Tasks and click OK. The Task dialog box for this new task will open with the e-mail as an attachment. Double-clicking on the attachment will open the original e-mail, but it will no longer be in your Inbox. Type in any additional information such as the date by which the task needs to be completed and click Save and Close

View original post 112 more words

Posted in Uncategorized | Leave a comment

Microsoft Excel – Formatting of Subtotals

ifonlyidknownthat

I was demonstrating subtotals to a client today and she wanted to know how to format all the subtotal rows in bold with a different background cell colour. The workbook we were using had Counts for the subtotal.

In the example below, I have created subtotals based on a count of how many of each product have been ordered. The subtotal text is in column C.

We selected the data area including the subtotals, then went to the Home tab and in the Styles group, from the Conditional Formatting dropdown, selected New Rule. We then clicked Use a formula to determine which cells to format. The formula we typed in was =RIGHT($C1,5)=”Count”. We then clicked Format. On the font tab, we selected Bold and on the Fill tab, we selected a background colour. We then clicked OK twice. The subtotal rows were formatted accordingly.


The RIGHT function…

View original post 76 more words

Posted in Uncategorized | Leave a comment