Excel – Transpose data quickly

Transposing data is easy using the built-in Transpose feature, but there is another way.  You can use Paste to transpose your data.

Excel tranpose data

  1. Select the data (table, spreadsheet) that you want to transpose.
  2. Copy it to the Clipboard using Ctrl+C. For instance, the example table (shown right) is in A3:E9.
  3. Select the top-left cell of the range you want to copy the transposed data to (A11 for instance).
  4. Click the Home tab (if necessary).
  5. Click the Paste drop-down in the Clipboard group.
  6. Click Transpose (the last icon on the second row).
  7. That’s it! Excel’s Paste feature quickly transposes the data.

Word – change the default formatting permanently

Word default documents are bland. Unexciting layouts, boring fonts, and most annoying that spacing between paragraphs.  You don’t have to settle for these default settings, you can personalize Word so that every time you start a new document it uses your preferred fonts and paragraph settings.

The trick is saving your preferences into the Normal document template, and you can do that automatically.default font

  1. Press Ctrl+Shift+F to open the Font dialog box.
  2. Choose your preferred font and font size.
  3. Click the Set As Default button and in the resulting dialog box, choose the All documents based on the Normal template
  4. Save your changes.

You can do the same with paragraph formatting. Right-click any text and choose Paragraph from the shortcut menu to open the Paragraph Formatting dialog box.  Choose your spacing options and click on Set As Default button.  No more unwanted spacing between paragraphs.

 

Systems backup plan

Information systems backup plan

backupSkyPoint Technologies recently published this article about business information systems backups.

Are you working on your computer system assuming that all your carefully entered information is magically secured and being backup up somewhere, somehow?  In the event of a serious system failure are you confident that all your information can be recovered?

Your critical business data is part of your competitive advantage in the marketplace and must be protected. 

We regularly come across businesses that believe they have a sound backup system in place.  Unfortunately, reality has proven otherwise.

These include:

  • Taking external hard drives or tapes off site (when staff remember); assuming the information is backed up on these devices.  Sadly we have found external devices completely blank, or corrupt with no recoverable business information on them.
  • Others have told us their backup is in the “cloud”, or replicated somewhere at another site.  However there is a misunderstanding as to how or what information is being backed up and how secure it is, let alone how to retrieve the information in the event of a system failure.

This is like playing Russian roulette with your company information.

SkyPoint suggestions?

  • Backup Integrity Testing – No matter what backup system is implemented, it must be tested and checked regularly. Simply receiving an email that your backup has successfully completed does not guarantee you actually have a backup.  Backup testing can vary from simple file recovery tests to full disaster recovery simulations, and is well worth carrying out long before you need them for real.
  • Offsite Backup Storage – Have some form of backup at another location.  A disaster such as a fire, flood, lightning or earthquake etc could mean that information stored at your premise may become inaccessible.  Many businesses in Christchurch couldn’t function after the earthquake because their information systems were either destroyed or not reachable.  This could have been avoided if their backup information was available elsewhere.
  • Image Based Backup Software – Ensure that your backup software is image based rather than file based. Imaged based backup software takes a snapshot of your complete system and makes data recovery a straight forward task.  File based backups only back up nominated files and folders, and not the whole system.  File backups are often implemented by external backup services available on the internet and requires a great deal more effort, time and money to rebuild.
  •  Hard Disk Backup Systems – Hard disk backup systems are an affordable and effective backup method.  Tape backup systems have been superseded by hard disks at the small to medium business end.  Unfortunately tape systems didn’t prove robust or reliable enough and required far greater time for data restoration.  Our recommendation is that if you still have a tape based system it is time to upgrade to a disk based system along with image based backup software.
  • Online Backups – The advent of uncapped ultrafast broadband has allowed image based backups to be sent to external locations.  These Online Backup systems are an excellent added safeguard to business information and are cost effective and efficient.  In summary, a good backup system could be the making or breaking of your business.  Hardware is easy to replace, but getting your business information back to a current and useable state is a completely different challenge. 

The SkyPoint team welcome the opportunity to discuss the various backup options available to you or give you a second opinion on the systems you already have in place.  Our advice is free and could save your business in the future. 

Please give them a call on 07 929 4932 or check out the website www.skypoint.co.nz for more details on business information systems backup.

Dummy Text also known as Filler Text

Dummy text is also known as ‘filler text.  It’ is generic text that is inserted so that you can see the formatting and layout.  This is especially useful when creating multicolumn frame-based layouts such as brochures, newsletters, documents and websites.

 Different applications use different words for dummy text; some programs repeat the same sentence over and over, use nonsense words or use pseudo-Latin phrases.  See the examples below:

=rand.old(1,2) =lorem(1,2) =rand(1,2)
The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas porttitor congue massa. Video provides a powerful way to help you prove your point. When you click Online Video, you can paste in the embed code for the video you want to add.

 

To insert dummy text, you use a random function such as =rand(p,s); where ‘p’ represents the desired number of paragraphs, ‘s’ represents the desired number of sentences.  For example, to get eight paragraphs with four sentences each use:

  • =rand(8,4) or
  • =lorem(8,4) to give you pseudo-Latin phrases

 This functions works only when it is typed as a paragraph all to itself.  It doesn’t work when typed as part of an existing paragraph or any other position. 

 Word 2013 however has real English language paragraphs that it uses, and they even make sense!  By default Word inserts five paragraphs if you use =rand() without any reference to number of paragraphs or sentences.  If you insert more than five paragraphs they start repeating.

 

 

Custom format large numbers in Excel

The more I learn and use Excel the more I use it to work smarter.  This tip will be very useful for those of you who work with and display large numbers (thousands and millions).  Large numbers can be difficult to read, especially if there are many of them.  The example below shows numbers in millions and in thousands; you decide which is easiest to read – normal or customised?  

To improve the readability of large numbers you can “custom format” them, but be careful you might get some unexpected results! 

Large numbers displayed in normal and custom formats

Custom large numbers excel

How to custom format numbers

  1. Select the data range and press [Ctrl]+1 to display the Format Cells dialog, OR right click and select Format Cells.
  2. From the Category list, choose Custom.
  3. In the Type control, enter the format string, examples displayed under Format Type section above.
  4. Click OK.

Additional notes

The “m” or “k” component displays the literal m or k character, to denote millions or thousands. 

Use one comma (,) to display thousands and use two commas (,,) to display millions or thousands.

To indent numbers from right, enter _) after the format string, see example above.

 

 

Show and hide the ribbon

Many people accidently hide the ribbon without knowing how they did it; this is very easily done.  When I did additional research on this tip, I found a couple of additional ways of hiding or displaying the ribbon.

Hiding the ribbon

This can be really useful when you want a larger working area and clear away the clutter.  Below are three ways in which you can show or hide the ribbon as required.

  1.   Click the up arrow icon at the right hand side of the ribbon.  Or right click on the up arrow and select ‘Minimize the Ribbon’ option. (Ribbon minimised)
  2.   Double-click one of the tabs at the top of the ribbon to toggle the ribbon on and off.
  3.   The keyboard shortcut – simply press [Ctrl]+[F1].

Outlook 2010 – How to quickly view your unread emails in your InBox

To quickly view your unread emails in your InBox is a very useful trick for those of you who get inundated with emails and are never sure if you have read or processed them all.

1.

Click on the Folder tab.

2.

Click on New Search Folder.

3.

Under the Reading Mail category, select Unread Mail.

4.

Under Customize Search Folder, select your mail account then press OK.

5.

You can now access your new Unread Mail folder (which may be a subfolder of Search Folders, as displayed below) for this email account.

Save yourself typing time using Quick Parts in Outlook

This month’s tip is using Quick Parts (available in Office 2007 and 2010). 

How often do you include the same bits and pieces of text/images in the emails you send again and again?  For example: directions; return policy details; thank you notes; answers to frequently asked questions; promotions and, disclaimers, etc.  If you haven’t discovered Quick Parts yet, you’re going to love this tip.

 How do I create a Quick Part?

The steps for creating a Quick Part building block are the same for Email, Calendar, Contacts and Tasks.  It doesn’t matter where in Outlook you create the item.  The saved block can be inserted into just about any Outlook item.

  1. Open a new email and type what text you would like to save in Quick Parts.  See example below.
  2. Apply any formatting to your text.
  3. Highlight the text passage, see below.
  4. Click on Insert tab on the ribbon.
  5. Click on Quick Parts from the Text Group.
  6. Select Save Selection to Quick Part Gallery.
  7. Name your Quick Part (remember to use a good descriptive name)
  8. If you choose you can add other info such as Category and a Description.
  9. Click OK.

Creating Quick Part text

  

  How do I use/insert a Quick Part?

  1. Put your cursor in your email where you want to insert the saved text.  (You can also use this text in contacts, calendar, and tasks.)
  2. On the Insert tab, in the Text group, click the little arrow on the right of Quick Parts.
  3. Click the Quick Part text that you want to use from your selection (if you’ve built in more than one).  That is it, so quick and easy to use.  Quick Parts is also available in Word.

Save yourself typing time using Quick Parts in Outlook.

Microsoft Office Word: Quickly displaying page layout dialog box

This time saving tip has been around for a while and it has stayed consistent, even with the new releases of Microsoft Office 2007 and 2010.  Anything that saves you a click or several clicks saves you time – this one is great, opening the Page Layout dialog box where you can define margins, layout, and so on.  You may find you use it a lot – I know I do.  Simply double-click anywhere in the greyed-tinted area of the vertical or horizontal ruler and the dialog box opens instantly.           

It’s the fastest and easiest way to open the Page Layout dialog box.

Inserting hyperlinks in Excel 2010

To insert hyperlinks within an Excel workbook, follow the steps below:

  1. Display gridlines for Print view.  Select [Page Layout tab] + Sheet Options + Gridlines.  Ensure View and Print check boxes are ticked.  (This option displays the page breaks within your workbook.)
  2. Insert a row at the top of your worksheet A1.  (Blank row inserted.)
  3. Select cell in row A where you want to insert your first hyperlink.
  4. Select Insert tab click on Hyperlink(Insert Hyperlink dialog box displayed as below).
  5. Select Place in This Document.
  6. Enter name/reference in Text to display field.
  7. Enter the cell reference in Type the cell reference and select which sheet if necessary.
  8. Click OK(Your new hyperlink will now be displayed, see examples below.)