K2 Office Tips Header
Volume 12, Number 5 

June , 2017
The weather has now warmed up enough that I can get out on our deck and enjoy our garden. The annuals got planted this week so it won't be long before the beds will be full of colour. 
I did my first keynote of the year last week at the Softrak AOC conference. It was great to visit with many old friends. This month I will be doing a session at Sage Summit.
Registration for the 2017 Accounting Technology seminar is now open and we already have over 400 registrations, which is a new record. This is the 25th year that we have traveled from coast to coast presenting this one day update on the technology that accountants, bookkeepers and their clients use. Because it is our 25th anniversary we will be celebrating. Each attendee will receive the 25th Anniversary webinar edition of my favorite Excel Tips. Many cities sell our quickly so CLICK HERE to register and hold your place.
A new website, Canadian Accountant, launched on Monday, May 29. This free site is devoted to news, trends and insights into the Canadian accounting profession. I have come out of writing retirement, and with with my partner Ward Blatch, and a number of well-known accounting journalists, will be providing monthly columns for this new venture. Check out Canadian Accountant at www.canadian-accountant.com or www.canadian-accountant.ca.
Our monthly Excel Magic mini-webinars have been a great success. The next two sessions will be in June in conjunction with Adagio by Softrak and VohCom Page. Every registrant will receive the recorded webinar so sign up today to ensure that you're on the list.    
As usual, I would welcome your comments at   alan@k2e.ca

Alan Salmo n

Excel Tips

ExcelTopDecChanging the Width of Bars in Excel Bar Charts
Summary: The default settings for bar charts leaves a lot of space between the bars. Here is how to reduce the space between the bars:
The default settings for bar chart in Excel mean that they appear on your charts with a lot of space between them. This default setting is sometimes not the look we want for our charts in particular if we want to compare the bars against each other. To have them appear closer together or of a thicker width would be better. It also will reduce wasted white space on the charts. It's easy to do this in Excel.
  1. Right Click on the bars
  2. Format data series
  3. Series Options
  4. Gap
  5. You will see a slider
  6. Slide left or right to change the % width between your bars
This will adjust the width of your bars making them closer together or further apart.
Summary: If you like using keyboard shortcuts as I do, here are some little known shortcuts that I find useful:
The keyboard shortcuts for Excel allow you to access almost everything on the ribbon and Quick Access toolbar. While the Quick Access toolbar shortcuts are subject to change, the ribbon shortcuts are predictable and worth learning.
You can use the Alt key to access the ribbon tabs. Excel labels each tab of the ribbon with a different letter. Selecting the letters F, H, N, P, M, and A will allow you to access different tabs of the ribbon. The Quick Access toolbar shortcuts are numbers 1 through 9, and then they start using two digits from 09 down to 01. 
After pressing Alt+H, Excel draws in new shortcut keys to access all of the commands on the Home Tab. Alt+C is Copy, Alt F+P is Format Painter, and Alt F+O is the dialog launcher for the Clipboard group.
Some of these keyboard shortcuts are obvious; for example, FS stands for Font Size and FF stands for Font Face. AL is Align Left. Other keyboard shortcuts make sense in a historical context; for example, Ctrl+V has meant Paste for 25 years, so it seems natural to use V for Paste. Some of the shortcuts don't seem to have any rhyme or reason; I have no idea why H is used for fill color
In some cases, a keyboard shortcut leads to a new flyout menu or gallery. Some items in that menu will have shortcut keys. Others might require using the arrow keys to select them.
Summary: Working with dates requires those dates to be in an Excel Date format. Here is how to convert a text date to one of the many Excel Date formats
  • Select the range of text dates.
  • Click the Data tab.
  • Select Data Tools | Text to Columns and a wizard dialog box will open.
  • In the wizard dialog box, click Next twice to go to Step 3 of the Wizard.
  • Use the default settings in the first two steps of the wizard.
  • In Step 3 of the wizard, select the Date option and select your desired format.
  • Click Finish and the text dates will convert to dates that can be formatted and used in calculations.


Word Tip


WordDec Copying and Moving Text Without Using the Clipboard
Summary: Sometimes you want to keep what you copy to the clipboard while copying or moving other text. Here is how to do this:

Many people use the Clipboard to copy text. This deletes what was previously in the Clipboard, replacing it with the selection you are copying. There may be times, however, when you don't want to disturb the contents of the Clipboard. Word allows you to bypass the Clipboard and copy text using the keyboard in this manner:

  1. Highlight the text you want to copy.
  2. Press Shift+F2. The message Copy to where? appears in the status bar.
  3. Move the insertion point to where you want the copy pasted.
  4. Press Enter.

If you want to move text instead, all you need to do is use F2 instead of Shift+F2.

K2E Canada Inc. is a leading provider of professional development seminars for the Canadian accounting world.  Each month we publish this free Office Tips e-mail newsletter. These tips will save you time and enhance the appearance of your Office files.
To subscribe to our K2E Canada newsletter  CLICK HERE
Your information is always kept secure and never shared.
Alan Salmon
Managing Director
K2E Canada Inc.