Monday, 21 March 2016

Project- Elapsed Time

There's elapsed time and then there's Project time. Project time is all about working time and therefore weekdays and holidays don't count. Bad luck when you've poured the concrete for your foundations on Friday so that it can set over the weekend. 3 days setting time means that construction work does not resume until the concrete has set on Wednesday. Huh?

Project time is always working time

That's because you set the duration to 3 days and as far as Project is concerned, that's 3 working days. You should have set the duration to 3 elapsed days so that you can crack on with the next phase as soon as the concrete has set.

Elapsed time includes non-working time

Every task needs a duration. The duration is the length of working time from start to finish that it will take to complete the task. Project sets the duration of Auto Scheduled tasks to one day unless you fill in a different value. The duration of Manually Scheduled tasks is calculated automatically from their start and finish dates, although you can override this by entering the duration and Project will calculate the finish date.

Valid Time and Date Units for Microsoft Project

You can enter durations in minutes (m), hours (h), days (d), weeks (w) or months (mo). Type the number followed by the abbreviation for the unit you want to use. For example, type 3d for 3 days or 6h for 6 hours. This uses time as defined by the Project Calendar, to use real time you should include an e prefix to any of the time units. For example, type 3ed for 3 elapsed days or 2emo for 2 elapsed months.

When you type durations like 3ed, 2w or 8h Project replaces the abbreviated units with more complete text. Type-in "d" and Project substitutes “days.” As you select a Duration cell, spinners appear so that you can click to increase or decrease the duration by one unit.

Word- Keeping Text Together with Non-Breaking Spaces

When Word calculates the line width and wraps your text onto the next line, the line break will occur at either a space or a hyphen. 

For example, if the text "100 km" or "Mr Bill Bloggs" does not quite fit at the end of a line, Word will insert a line break between "100" and "km" or between "Mr" and "Bill Bloggs". Non-breaking spaces prevent this from happening.

The non-breaking space guarantees that the text will not be broken; if it does not fit at the end of a line then the whole thing is moved to the next line. Whenever you want to make sure that two or more adjacent words appear on the same line as each other use non-breaking spaces instead of regular spaces. To enter a non-breaking space hold down both the Ctrl and Shift keys as you press the Spacebar.

Show/Hide

Different versions of Word have different visual indicators for the non-breaking space when you click the Show/Hide control. Normally a space is shown as a dot on the text centre-line, in Word for Mac the non-breaking space is the dot with a wavey line above. In Word for Windows the non-breaking space is displayed as a small superscript circle. Try the shortcut key Option+Spacebar on your Mac as an alternative to Ctrl+Shift+Spacebar.

Related Posts

Word-Typing in Accented Characters

Excel Pivot Tables- Flattening a Cross tab (Normalising data)

Data for a human being
We usually work with and better understand data which is presented two-dimensionally in a matrix or cross-tabulation. In other words, the classic spreadsheet with headings up the top, descriptions down the side and data in the middle.
Data for a machine

But computers don't work like that, they need data in one-dimensional normalised tables. When you need source data for an Excel Pivot Table or for any sort of database table you have to "unpivot" a cross-tabulation into a simple list.

Converting one data presentation from one to the other is a horrible task involving a significant labour of moving and copying data. But not if you use Excel's old Pivot Table Wizard method.

Firstly, you have to get the shortcut trigger for the Wizard as it is not available on the ribbon and then you create a Pivot Table on Multiple Consolidation Ranges and from that you can easily generate your normalised data.

Getting the shortcut onto the Quick Access Toolbar 

More Commands
You only have to do this once, after that you can use it as many times as you like. To change Excel's Quick Access Toolbar you can either click File tab, Options and Quick Access Toolbar or (far easier, I think) click at the end of the Quick Access Toolbar (top left of the Excel window) and choose More Commands.

Click PivotTable and PivotChart Wizard from the Commands Not in the Ribbon category. Finally, click the Add button to add it to the list on the right and click OK. Job done.

PivotTable and PivotChart Wizard, Commands Not in the Ribbon

Creating the Pivot Table

Multiple consolidation ranges
Click on any cell in your cross-tab data and then click the PivotTable and PivotChart Wizard shortcut that you've just created.

In Step 1 of 3 of the Wizard click the Multiple consolidation ranges option under Where is the data that you want to analyze? and the PivotTable option under What kind of report do you want to create?

Then click Next.




I will create the page fields
In Step 2 of 3 of the Wizard click the I will create the page fields option under How many page fields do you want?

Then click Next.







Select your range and click Add
Step 2b of 3 of the Wizard, select the range containing your cross tab data and then click the Add button to complete the All ranges list box.

There's no need to go to Step 3, click the Finish button and Excel inserts a new worksheet and displays your dataset as a Pivot Table.


Normalising the Data

Double-click the Grand Total cell
It looks rather unexciting, all you've done is recreate the original data but as a Pivot Table with a few totals. Now comes the exciting bit, double-click the Grand Total cell.

We all know what happens when you double-click a Pivot Table cell, it's the shortcut for Show Details and quick as a flash all your data is deconstructed and returned as a table onto a new worksheet. Normalised data in a simple list, how easy was that?

Normalised data
If you've ever had to do this sort of thing the long way round you will now want to kick yourself and weep.

If you don't want your data in an Excel table format, click the Table Tools tabs and look for the None format under the Light category in the Table Styles gallery and then the Convert to Range control in the Tools group. I can never see these because I am too busy weeping. Yes, I have frequently done this the long way round.

Mac OS X Rename Files Keyboard Shortcut

Renaming a file
Do you ever get that sinking feeling inside when you realise that you seem to have entirely missed out on something that's screamingly obvious? Something that everybody else knows about but you don't? Happens to me all the time.

My latest one is renaming files in Mac OS X Finder. I am used to performing a delayed click on the file name, sort of click to select the file and then click slowly again to enter rename mode. It works fine most times but it can be a bit annoying when you manage to double-click and open the file when you only wanted to rename it. This usually happens when you have a whole batch of files that you want to rename. When it gets really annoying I used to resort to pressing ⌘-I for the Info window and rename there. Used to?

Just Press Enter!

I say used to because I stumbled upon the keyboard shortcut quite by accident. To rename a file in Finder all you have to do is click to select the file and then press Enter or Return. It really is as easy and as obvious as that.

Now you can tell me that surely everyone knows that...

Related Posts

Sunday, 6 March 2016

Excel- Removing Space Characters the Easy Way

Space Invaders!
Space invaders—they’re out there. They’re everywhere, so many people who spent their tender youth learning to touch type seem to have the dreadful habit of entering a space character after every word they type.

I suppose it's understandable, whenever they type in a word their thumb hits the space bar because you need a space between each word in a sentence.

The trouble is they bring this habit with them when they type individual words into Excel cells. They don't mean to but they do. Bang goes that spacebar again and there you are with a space on the end of every single word. The Space Invaders strike again and it's Game Over for your formulas.

Space Invaders

Your text is corrupted, you can’t see the space so you don’t know it’s there and you go crazy every time you use one of Excel’s functions which relies upon simple text matching to work properly. And it's usually the last thing you think about, all you know is that your formulas don't work! It's usually formulas that use one of those incredibly useful functions like SUMIFS, COUNTIFS and VLOOKUP. Now, who in their right mind would want to use those? Well, just about everyone. 

Consider the bitter tears of wrath that have been spilt over such an innocent thing as a space character. It's usually "Boo-Hoo-Hoo, why isn’t my formula working?" Then, after half an hour of head scratching, it's.."Duh, it's got a space on the end." Been there?

Database data

The other explanation for the dreaded spaces is that your Excel data has been retrieved from a database file and the original table data has a fixed length field. Unless someone has remembered to clean up the data when it is extracted you can end up with the actual data you can see plus the padding of space characters that you can't see. A typical fixed length field for text data is 50 characters.

Trim function

Once you realise that the space characters are there (and that is the difficult bit) then you can easily remove them using Excel's TRIM function which trims off the space characters before the first and after the last visible character. The leading and the trailing spaces are removed but any spaces in the body of the text, the medial spaces, are preserved. But you can't clean up your data in situ, you have to enter a formula into another cell and then Copy and Paste As Values to replace the original dirty data with your clean data. It works but it takes a few steps and it's painful if you have to do this kind of thing on a regular basis. Maybe there's a better way?

Macro to remove spaces from all text cells in a worksheet

Try using my macro, it takes no time at all and trims the spaces in all your text in the entire worksheet. You don't need to know the first thing about macros to get it working. If you can copy and paste then it's yours. This is what you need to do:

  1. Create a module in your Personal Macro workbook where the macro will be stored.
  2. Copy and Paste the macro code into your module.
  3. Save your Personal Macro workbook.
  4. Run the macro.

Step By Step

Create a module in your Personal Macro workbook 

Turn on the macro recorder and record into your Personal Macro workbook. It doesn't matter what you record because we are going to replace the recording with our own code in a moment but you need to have a module (a storage area for macros):

Recording into Personal
Click View tab, Macros and Record Macro. Don't worry about the Macro name or anything else apart from the Store macro in setting which should be Personal Macro Workbook which you can choose from the drop down list.

Click OK to start the recording and then click Stop Recording from the Macros control. You now have your module.


Copy and Paste the macro code into your module 

Select all the text from the section below and copy it (all the text starting with and including the word Sub down to and including the last line, End Sub) Now we need to switch over to your module and paste the code.

This is the code to copy and paste:

Sub Trimmer()
  Dim rngText As Range
  Dim rngItem As Range

  On Error Resume Next

  Set rngText = Cells.SpecialCells(xlCellTypeConstants, 2)
  
  For Each rngItem In rngText
      rngItem.Value = Trim(rngItem.Value)
  Next

End Sub

Press ALT+F11 to switch over to the Visual Basic Editor, then look at the top left hand corner of the editor window and see if you can spot the Project Explorer window. Press CTRL+R if you can't see it. Look for PERSONAL.XLSB in the listing and keep clicking the plus signs until you can see the modules in your Modules folder. Double-click on Module 1 to open it. If you have more than one of them (you've already been using your Personal Macro workbook) then double-click the one with the highest number. Select your recorded macro and Paste over it.

The Recorded macro viewed in the Visual Basic Editor

This is what your macro should look like. Notice how some of the words turn blue:

Macro pasted into the module

Save your Personal Macro workbook

Do this while you are in the Visual Basic Editor as it's tricky to do back in Excel. Click File, Save in the main menu. Press ALT+F11 to exit the editor and return to Excel.

Run the macro

Run the macro
Our macro is ready to go now so open the workbook file where you want to clean up all the text and then click View tab, Macros, View Macros. Choose PERSONAL.XLSB!Trimmer from the list and then click Run. Voila and Hey Presto! Quick as a flash your text is cleaned. Where did all those spaces go?

Your Personal Macro Workbook opens up automatically every time you start Excel so once you've saved it you can use it anytime you like. The Personal Macro workbook is a hidden workbook that's used to store handy macros that you would want to use on a regular basis, it's always open but it's not visible.

Commentary of the macro code

If you know all about macros then you can just copy the code and use it. If you want to analyse it then here's the explanation:

Firstly, we declare two object variables of class Range; rngText which is a collection of all the text cells in the active worksheet and rngItem, which is each individual text cell—each member of the collection. 

Dim rngText As Range
Dim rngItem As Range

Then we initialise rngText using the Set keyword and loop through the collection with a For Each loop returning the cleaned text into each cell using the VBA Trim function.

Set rngText = Cells.SpecialCells(xlCellTypeConstants, 2)

For Each rngItem In rngText
      rngItem.Value = Trim(rngItem.Value)
Next

Friday, 26 February 2016

Company News

Updates to our City training office

Time has flown by since we opened our first venue at Worship Street in the City. Our first goal was to update the previous tenant's offices not just with a new coat of paint but with all new furniture and equipment, new computers and ceiling mounted projectors.

Facilities at Worship St.
One of our most popular customer requests has been for faster internet. Like many offices in the City our service was restricted due to poor infrastructure and we were mighty unhappy with a very slow and unreliable internet connection. Not very 21st century!

We are now happy to announce that we can offer high speed internet in all our training suites. In the past week we have had great feedback from attendees on our Sharepoint courses.

Course Materials

Last year we released the new customer dedicated area on our website for all new IT manuals. Our manuals are updated on a regular basis and in the coming weeks we will be releasing manuals for the new Microsoft Office 2016 titles.

Let us know if you have forgotten your password, mike.barrett@catraining.london

Hot Courses

Last year saw the release of the new Microsoft Office 2016 versions for Window and Mac. We expected the new version to be popular with the Office 365 subscribers but it is surprising how many people have migrated to the newer versions. Looking back on our activity for January we can see that Office 2010 is still the most popular, followed by 2016 then 2013 users. We delivered more Project 2016 courses than any other previous Project version combined.

Our course rates for 1-2-1 and groups are frozen for the second year. Our Public courses were very popular last year and we shall be offering even more this year. We also have a series of dedicated courses for those studying for the Microsoft Office accreditation.

Microsoft Accreditation

Last December we became a Certiport accredited testing centre. We have had a lot of interest from existing customers wanting to find out about the benefits of accreditation, which exams are available and prices. See our website for further details on Microsoft Accreditation

Why bother with Accreditation?

The MOS Badge
Some people ask why should they bother being accredited. The view of one HR Manager view was that it was to test his colleague’s performance readiness. How many of us have used Excel or Word for years but still do not take advantage of their new functionality?

The Microsoft tests are challenging, they test you on some old topics as well as the new functionality. There are a range of tests to suit all, pass four and you become a recognised Microsoft Office specialist.

There’s no written test, the exam is a series of interactive computer tasks which you have to perform. The whole process takes less than one hour to complete.

Sunday, 7 February 2016

Windows 10- Quick Access to easily find files

If you are having difficulty in navigating your folders and drives and are forever searching for files then you should definitely consider taking advantage of Quick Access to create shortcuts to all your important folders. Your Quick Access list is available when you open the Windows File Explorer. Earlier versions of Windows have a similar arrangement called Favorites.

Manipulating files and folders in Quick Access does not change or alter the original files or folders in any way. Quick Access is only a pointer to the original files on your computer. Removing a file or folder from Quick Access does not remove or delete the original.

Quick Access in File Explorer

Removing Folders from Quick Access

The Microsoft File explorer always includes some Quick Access locations for you that you may not want. The default locations are usually Desktop, Downloads and Documents etc.

Remove from Quick access

To delete the locations you don't need, simply right-click on the location and choose Remove from Quick access from the shortcut menu.

Adding Folders to Quick Access

Adding your favourite folders is easy. Click the folder that you want to add, right-click and choose Pin to Quick access from the shortcut menu. You can also drag and drop a folder over onto Quick access to create a link for it.

Pin to Quick access

In the illustration above I have been meaning to file all my proposals in my "Proposals" folder but the folder is buried in my directory structure and I can never remember where it is, so I've just been saving my work on the desktop and neglecting my filing. A recipe for disaster. 

Quick Access shortcuts

Now, it's so easy to see your files, you don't have to worry which drive or folder they're stored in and you'll see the Quick Access listing whenever you open or save a file. Can you see how I've caught up with the filing?

Show only pinned folders

Windows automatically adds frequently used files and folders to your Quick Access list which can be a great boon or a major irritation depending on your point of view. To turn off recent files or frequent folders, click the View tab, then click Options. In the Privacy section, clear the checkboxes and select Apply. Now your Quick Access list will only show your pinned folders.

Related Posts

Handy Windows 10 tricks

Thursday, 28 January 2016

One Drive for Business Help Guide

Managing your files in OneDrive for Busines


 

 


 

Credits

Microsoft Office Training Centre

Editorial

Charis Alexandra Training

 
Managing your files in OneDrive for Business

After you sync your OneDrive for Business files with your computer, you can put away your browser and manage your files like other files on your computer. Need to add a file to OneDrive for Business? Just drag it to your OneDrive for Business folder. You move, rename, and delete your files the same way you're used to, with one important difference: the changes you make to OneDrive for Business files will sync to all your other devices. So if you delete a file here, it's deleted everywhere.
Copy or move files in OneDrive for Business

 

  • In File Explorer, select the files you want to copy or move.
  • Right-click the files and drag them to your OneDrive - YourCompanyName folder or to another location.

Moving a file into OneDrive for Business from File Explorer

  • Select Copy here or Move here on the shortcut menu.
Rename a file in OneDrive for Business

  1. Right-click the file you want to rename in your OneDrive - YourCompanyName folder.
  2. Select Rename on the shortcut menu.
  3. Type the new name for your file.


 
Delete a file in OneDrive for Business

  • Select the file you want to delete in your OneDrive - YourCompanyName folder.
  • Press Delete on your keyboard.

Tip   Remember, any changes you make to a file in your OneDrive for Business folder apply to all devices, not just to this computer. The changes will sync to all your other devices.
Dragging and dropping files to OneDrive for Business

You can upload files to your OneDrive for Business by just dragging them from your computer. OneDrive for Business shows you where you can drag and drop files to add files to a library.

  • At the top of the page, select the Office 365 app launcherOffice 365 app launcher icon, and then select OneDrive.

Using the app launcher in Office 365 to go to OneDrive for Business

  • Find the documents that you want to upload on your computer and drag them to your OneDrive for Business.

Drag and drop to upload files to OneDrive

Notes   

  • The first time you click OneDrive, you see some setup screens and instructions while your personal site is being set up. You may have to wait awhile and then click OneDrive again before continuing to step 2.
  • If you don’t see the option to drag files, update your copy of Office.
  • The Edge browser doesn't support drag and drop into OneDrive for Business. Internet Explorer 11 is supported.


Here’s another way to upload files:

  • Instead of dragging files, select Upload.

Upload command in OneDrive for business

In OneDrive for Business you can choose Files. Use the Open dialog box to select the files you want to upload, and then click Open.

For SharePoint Online use the Add a Document dialog box. Click Browse and select the files you want to upload. Click Open and then click OK.

 

Note   You can only drag and drop, or upload files in OneDrive for Business or SharePoint Online. If you need to upload folders, consider syncing the folder with your computer. For more information on syncing, see Sync OneDrive for Business or site libraries to your computer

Tip   Your files are private unless you decide to share them. To share files easily with everyone in your organization, drag them to your Shared with everyone folder. For more information, see sharing files with others.
Seeing your files from other devices

After you upload files to OneDrive for Business, you can see them from other devices by just signing into your Office 365 site in your browser, and then clicking OneDrive. Here are the details.

  • On any device, sign in to Office 365 with your work or school account. For example, ellen@contoso.onmicrosoft.com, ellen@contoso.edu, or ellen@contoso.com. If you're not sure what your work or school account is, check the welcome email that asked you to sign in the first time.

Sign in screen for Office 365

  • At the top of the page, select OneDrive. Or, select the app launcher Office 365 app launcher icon, and then select OneDrive.

Office 365 navigation options

  • The files you upload from any device are right there for you to use.

Tip   Your files are private unless you decide to share them. To share files with everyone in your organization, upload or move them to your Shared with Everyone folder. For more about sharing, see Sharing files with others.
Set up Office Mobile on your phone or tablet

To get your Office Mobile apps to work with Office 365, choose your device.

Icon for Android Phone
iPhone
Icon for iPad
Windows Phone

 
Saving and opening files

You can work with the files you store in OneDrive for Business right from Word, Excel, PowerPoint, and other Office desktop apps. There's no need to go to your Office 365 site in a browser.
Save a file to OneDrive for Business from an Office desktop app

With a document open in an Office desktop app, click File > Save As > OneDrive - YourCompanyName. For example, OneDrive - Contoso.

Saving a file from Word to OneDrive for Business

  • Browse to the location where you want to save the file, and then click Save.
Open a OneDrive for Business file from an Office desktop app

  • In an Office desktop app, click File > Open > OneDrive - YourCompanyName. For example, OneDrive - Contoso.

Opening a file from OneDrive for Business in Word

Browse to the file you want to open, and then click Open.

Tips   If you don't see OneDrive - YourCompanyName, click Add a Place > Office 365 SharePoint. Then sign in to Office 365 with your work or school account.

Adding OneDrive for Business as a place to save to in Word

Once you add OneDrive for Business as a place in one Office app, you can save files from all your Office apps.


Syncing your files with your computer

Sync OneDrive for Business to your computer, and then get to your files in File Explorer instead of in a web browser. All your changes sync to OneDrive for Business whenever you’re online.

  • At the top of the page, select OneDrive. Or, select the app launcher Office 365 app launcher icon, and then select OneDrive.

Office 365 navigation options

  • On your OneDrive for Business page, click Sync.

Syncing OneDrive for Business to your computer

  • At the prompt, click Sync Now to start the sync app.
  • The app automatically fills out the name and web address of your OneDrive for Business library.
  • Select Sync Now in the app to start syncing.
  • You can click Show my files in the app to open the synced OneDrive for Business folder in File Explorer. The folder appears in your Windows Favorites as OneDrive for Business or OneDrive - YourCompanyName.

OneDrive for Business in File Explorer in Windows

Note   To sync files to your computer, you need the OneDrive for Business sync app, available with an Office 365 subscription that includes Office 2013 desktop applications. If you don’t have Office 2013, you can download the OneDrive for Business sync app.
Managing your files in OneDrive for Business

After you sync your OneDrive for Business files with your computer, you can put away your browser and manage your files like other files on your computer. Need to add a file to OneDrive for Business? Just drag it to your OneDrive for Business folder. You move, rename, and delete your files the same way you're used to, with one important difference: the changes you make to OneDrive for Business files will sync to all your other devices. So if you delete a file here, it's deleted everywhere.
Copy or move files in OneDrive for Business

  • In File Explorer, select the files you want to copy or move.
  • Right-click the files and drag them to your OneDrive - YourCompanyName folder or to another location.

Moving a file into OneDrive for Business from File Explorer

  • Select Copy here or Move here on the shortcut menu.
Rename a file in OneDrive for Business

  • Right-click the file you want to rename in your OneDrive - YourCompanyName folder.
  • Select Rename on the shortcut menu.
  • Type the new name for your file.
Delete a file in OneDrive for Business

  • Select the file you want to delete in your OneDrive - YourCompanyName folder.
  • Press Delete on your keyboard.

Tip   Remember, any changes you make to a file in your OneDrive for Business folder apply to all devices, not just to this computer. The changes will sync to all your other devices.
Sharing files with others

  • When you store your files in OneDrive for Business, you can share with others from any device by going to your Office 365 site in a browser.
  • You can also share a Word document, PowerPoint presentation, or Excel spreadsheet right from within Office without even going to Office 365 in a separate window. Whichever way you choose to share Office files, you can work with others at the same time they work on a file and see the changes that other people make as they make them.
Share a file from a site

  • At the top of the page, select OneDrive. Or, select the app launcher Office 365 app launcher icon, and then select OneDrive.

Using the app launcher in Office 365 to go to OneDrive for Business

  • Select the file you want to share, and then select Share.

Sharing a file from your OneDrive for Business library

  • In the Share window, type the name of each person you want to share the file with. As you type each name, suggestions appear that match your contacts. When you see the name you want, select it to add it to the invitation list.

Select people to share document with.

Tip   To share files with everyone in your organization, type Everyone instead of the name of a person.

  • Select the permission in the list that you want to grant people.
  • You can change the permission later for any person.
  • If you want, type a message to send to all the people you're inviting.
  • The email message sends a link to the shared document.
  • If you don't want to send an email to invitees, clear Send an email invitation.
  • Click Share.
Share a file from an Office desktop app

  • With the file open in Word, Excel, PowerPoint, or another Office app, click File > Share > Share with People.

Sharing a document from Word 2013

  • in the Share dialog, click Share with people.

Sharing a document from Word 2016

  • Type the name of each person you want to share the file with. As you type each name, suggestions appear that match your contacts. When you see the name you want, select it to add it to the invitation list.
  • Select the permission in the list that you want to grant people.
  • You can change the permission later for any person.
  • If you want, type a message you want to send to all the people you're inviting.
  • The email message sends a link to the shared document.
  • Click Share.
Work on files with others at the same time

  • When you store and share your files in OneDrive for Business, you can work with others at the same and avoid reconciling multiple versions of your files. Work together from either the online or the desktop versions of Word, PowerPoint, or OneNote. For workbooks, use Excel Online. If someone opens the workbook in the Excel desktop application, the workbook can’t be edited in Excel Online until it’s closed again in desktop Excel.

Two people are co-authoring


Here are a few details to keep in mind as you work with others:

  • In the desktop programs, co-authoring works best in the most recent version of Office (Mac and Windows), but is also supported in Office 2010.
  • There's no special co-authoring mode and no command to begin working together on a document. Just open the file for editing and start working.
  • As you edit, the Office app tells you when other people are working too. In Word, you'll even see which paragraph they’re working on.
  • Updates are handled differently in some programs from others. For example, OneNote notebooks and Excel Online workbooks show updates immediately. In Word documents, save the document to share your updates and to see others’ updates.