Infero Training – Business and Technical Courses

So you are currently using Excel to store a list of information but now that list has gotten too big to keep on scrolling through and has become a time consuming chore? You should import your data into Microsoft Access which offers you powerful database tools to handle all the information.

To start importing your data, follow these steps.

Step 1: Open Access and create a blank database. Once you have done this, Access automatically brings up a table. You can close this table as it isn’t needed.

Step 2: Click the External Data tab at the top of the page and then select Excel in the Import category which will in turn bring up the Get External Data – Excel Spreadsheet wizard to help you input the data.

Step 3: Browse for the worksheet which contains the information that you would like to put in the Access database and then select the button labeled Import the source data into a new table in the current database. Click on OK to continue.

Step 4: If you have different worksheets in the same document, select which worksheet it is that you want to import and then click Next to continue.

Step 5: Next, if the spreadsheet you’re using has column heading in its first row, click the box, if not names will be generated and you’ll be able to change them later on. Then you click on Next again.

Step 6: The next screen gives you the option to leave any column that you don’t want to import out. In order to do this, you start by clicking on the first column in the preview area. If you don’t want to import that column into the table, put a check mark in the box next to Do not import field and move on to the next column. To change the name of a field, use the Field Name box to change it and then just select which type of data is in the column itself.

Step 7: Next you have to choose a Primary Key if you want one.

Step 8: Input a name for the table in the box under Import to Table and then click Finish.

Step 9: You’ll now be asked whether you want to save the import steps or not. If you are importing more than one worksheet with the same layout then you can choose this option to save you time importing future worksheets. Click close and your new table will be shown in your database.

Whilst using Microsoft Excel, do you save all your Excel worksheets to a specific folder and are tired of searching for that folder each time you want to save a worksheet? Well in this tip, I will tell you how to set Excel up so that it saves to your designated folder each time.

First of all, click on the Microsoft Office button in the top left hand corner of the page and then scroll to the bottom to Excel Options.

You then select the Save tab at the side and look for the Default File Location box. In that box you put the folder which you want to save your Excel worksheets to which you can just copy and paste from the folder itself.

Then just click Save and you’re done.

Click to view Microsoft Excel Training Courses that Infero currently offers.

Click to view more Microsoft Excel Tips.

In Microsoft Word, when you make a spelling mistake in your text, Word automatically shows you that you have made a mistake by underlining the affected words with a red line.

I was asked this week to see if there was a way to get Microsoft Excel to act like Microsoft Word in the way that it recognizes spelling mistakes automatically and highlights them to bring them to your attention. After looking into this I found that unfortunately there isn’t a way to get Excel to recognize and highlight any mistakes you make whilst filling in the spreadsheet. However to correct any mistakes that you have made in Word, you can either press the F7 key on your keyboard or right click over the mistake and Word will offer you suggestions for the correct spelling.

To turn on/off this feature, follow these steps:

Click the Microsoft Office Button in the top left corner, and then click Word Options and select Proofing.

If you want to just turn the checker on for the document you are working on, do the following:

Under Exceptions for, click Name of currently open file and select or clear the Hide spelling errors in this document only and Hide grammar errors in this document only check boxes.

If you want to turn the checker on/off for all documents you create from now, do the following:

Under Exceptions for, click All New Documents and select or clear the Hide spelling errors in this document only and Hide grammar errors in this document only check boxes.

The only tool that comes close to doing this job in Excel is the Auto-correct option which can identify and correct common mistakes and typos such as replacing teh with the.

Want more tips? Click on Microsoft Word and Excel Tips.

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