Infero Training – Business and Technical Courses

While entering data in Excel worksheet you may find yourself using the keyboard a lot. If you take your hands off the keyboard to use the mouse, it will slow you down. An alternative is to use keyboard shortcuts instead of a mouse. Below are my 10 most favoured key shortcuts in Excel 2007! To use them, simply press two keys shown in the brackets < > simultaneously.

<Ctrl> + <A> – Selects an entire worksheet

<Ctrl> + <C> – Copies the selected cells

<Ctrl> + <V> – Pastes the selected cells

<Ctrl> + <X> – Cuts the selected cells

<Ctrl> + <Z> – Undoes the previous command, if possible

<Ctrl> + <Y> (or <F4>) – Repeats the last command or action, if possible.

<Ctrl> + <S> – Saves the current work

<F2> – Enables editing within a cell

<F11> – Creates a chart from the selected data.

<Alt> + <=> Inserts the AutoSum function.

If you are working on a very important worksheet, use <Ctrl> + <S> regularly to prevent your data from being lost accidentally.

Want more tips? Click on Microsoft Excel Tips & Tricks

If you find that gridlines in your Excel worksheets are distracting, make them invisible!

To turn off the gridlines in Excel 2007, follow these steps:

1. From the Ribbon, click the View tab.
2. In the Show/Hide section, clear the Gridlines check box.

Now your gridlines are hidden.

Want more tips? Click on Microsoft Excel Tips & Tricks

You probably get very upset when your Microsoft Office application crashes down (closes down unexpectedly) while you are working on something important. It is not necessarily that the problem is within the Microsoft Office code. Computer viruses (read How to Protect Yourself from Viruses from Your Computer Health Part Two), disk failures, or laziness with respect to installing updates (read How to Stay up to Date Automatically from Your Computer Health Part One) are just a few issues causing this condition.

Microsoft Office Diagnostic is a tool included in Microsoft Office 2007 pack, allowing you to run a series of diagnostic tests that can help you discover why your computer is crashing. These tests will either solve problems directly or may help you with the ways to solve them.

These are the diagnostics that are run:

Setup Diagnostic – Files and registry are checked for errors occurring due to viruses or by hardware that is faulty or incorrectly configured. While running for about 15 minutes, the test may require the original installation source.

Disk Diagnostic – Hard disk is examined for errors logged by the Windows system and SMART (Self-Monitoring, Analysis and Reporting Technology) feature of your hard disk (if provided by disk drive manufacturers) to notice potential hard disk failure.

Memory Diagnostic – Random Access Memory (RAM) is verified for integrity.

Update Diagnostic – Your computer is tested as to whether it has the latest updates installed to make Microsoft Office more stable. You can install updates from Microsoft Office Online.

Compatibility Diagnostic – Different versions of Microsoft Office are checked for conflicts, as two different versions of Office installed on your computer can cause instability.

Check for Known Solutions – The crash report is tested and checked against the database of Microsoft’s crash cases for available solutions.

To find out how to run diagnostics, read How to Fix Errors Using Microsoft Office Diagnostics.

Creating a chart in Microsoft Office Excel is a very simple process. Excel provides a variety of chart types that you can choose from. A chart can be embedded as a graphic object on a worksheet page, or it can appear on a dedicated chart sheet.

For most charts, such as Column and Bar charts, you can create the chart by plotting the data displayed as rows and columns. However, some chart types, such as Radar, Pie or Bubble charts, require specific data arrangement.

To create a chart:

1. Select the cells that contain the data you want to use for the chart.

 

2. On the Insert tab, in the Charts group, either:

• Click the chart type, and then click the chart subtype you want to use
• Or, to see all available chart types, click on the small arrow in the bottom right corner to display the Insert Chart dialog box. Then scroll through and select the one you want to use.

The chart will be placed on the worksheet as an embedded chart

Once the chart is created you can modify its structure by using Design, Layout and Format tabs within the Chart Tools toolbar.

The Ribbon is designed to help you quickly find the commands. Commands are organised in logical groups that are collected together under tabs according to a type of activity.

You cannot delete or replace the Ribbon with the toolbars and menus from the previous versions of Microsoft Office. However, to make more space available on your screen, you can minimise the Ribbon.

1. From the Quick Access Toolbar, click Customize Quick Access Toolbar .
2. From the drop down list, select Minimize the Ribbon.  

To use the Ribbon while it is minimised, click the tab you want to use, and then click the option or command you want to use. After your request has been performed, the Ribbon reverts to being minimised.

Let’s say you want to format cells, so that you can display preceding zeroes for a number, for example, a phone number will always begin with a zero.

1. Select the cells that you want to format.
2. Right click on selected and from the menu choose the Format Cells
3. From the Number tab in the Category click on Custom.
4. Click in the Type box, delete the displayed content and enter zeroes that correspond to the size of the required number, for example if you want to display 5 numbers, enter 00000.
5. Click OK.

When you enter numbers into the formatted cells, the preceding zeroes will be displayed, for example if you formatted the cells as 00000, when you enter 123 you will get 00123

You finally installed Office 2007 and guess what? You cannot find commands you are used to in the Menu!

It will certainly take a while to adjust to a new Menu. So what you can do to locate where your favourite Office 2003 commands are in the new Office 2007 interface?

Microsoft developed visual, interactive reference guides to help you quickly learn where things are. You can download and install them on your computer.

The only downside to it is that you have to download these guides for each of the Office 2007 Applications.

If you are not sure if it will help it, try an online demonstration from one of those listed at Microsoft Office Online.

If you are happy to proceed, use the following links to download command reference guides for each of the Office applications:

Word 2003 to Word 2007 interactive command reference guide

Excel 2003 to Excel 2007 interactive command reference guide

PowerPoint 2003 to PowerPoint 2007 interactive command reference guide

Outlook 2003 to Outlook 2007 Interactive Command Reference Guide

Access 2003 to Access 2007 interactive command reference guide

PDF is a fixed-layout electronic file format that preserves document formatting. It ensures that when the file is viewed online or printed, it retains exactly the format that was initially intended, and that data in the file cannot easily be changed.

Before the release of Office 2007 we had to either buy Adobe Acrobat for about £450 in order to convert Office files into PDF format, or install a third party software solution.

With the release of Microsoft Office 2007, Microsoft introduced a file format called XPS. It is similar to PDF in that you can save a file to this format and share or print it at a later date. This XPS format is not the global standard, and not many people know what it is when they receive it.

The following Office applications can be used to convert files to PDF format:

• Access
• Excel
• InfoPath
• OneNote
• PowerPoint
• Publisher
• Visio
• Word

In order to convert files, you need to install 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS. 

Let’s use Microsoft Word application to convert a Word file into PDF format.

1. Open a Microsoft Word document you want to save in PDF format
2. Click the Microsoft Office Button , and then navigate to Save As, and click PDF Or XPS.

 

 

3.  In Save as Type, make sure that PDF is selected.

 

 

If you want to open the file immediately after saving it, select the Open file after publishing check box. This check box is available only if you have a PDF reader installed on your computer.

 

4. Click Publish to create your PDF.

You may have noticed when typing into a column that if you start to enter some text that begins with the same letters as text that has been previously entered in that column, Excel completes the remaining characters for you. This feature is called AutoComplete.

It will not always work, for example, for entries that contain only dates or numbers.

If the first few characters that you type in a cell match an existing entry in that column, Excel automatically enters the remaining characters for you. Excel automatically completes only those entries that contain text or a combination of text and numbers. Entries that contain only numbers, dates, or times are not automatically completed.

You have the following choices:

• Accept a proposed entry by pressing ENTER (in this case the completed entry will match the pattern of uppercase and lowercase letters of the existing entry)
• Replace the automatically entered characters by ignoring the proposed entry and continue typing.
• Delete the automatically entered characters by pressing BACKSPACE.

However, if you find this feature is not useful, you can turn it off:

1. Click the Microsoft Office Button , and then click Excel Options.
2. Click Advanced, and then, under Editing options, clear or select the Enable AutoComplete for cell values check box and turn automatic completion of cell values on or off.

Suppose you need to shift the content of rows with the content of columns. What shall you do?

The Transpose option shifts rows and columns on the worksheet. For example, if you have a table where the months appear as rows and the sales as columns, the transpose option will reverse the rows and columns.

You need to do the following:

1. Select the range you want to change
2. From the Home tab in the Clipboard group, click the Copy button
3. Select a cell outside of the range you copied
4. In the Clipboard group, click the Paste drop-down arrow and select Transpose