Posts Tagged 'Excel'

Problems transitioning from Excel 2003 to 2007

For a basic explanation on how to find your way around the Excel 2007 menu / ribbon see 23Nov10 post:
https://myitbits.wordpress.com/2010/11/23/excel-2007-ribbon-v-2003-menus/

Install “Add-ins”

How do you activate “add-ins” in 2007? It’s hidden away …

  1. click that Big Office button
  2. Excel options
  3. Add-ins

There is a good full explanation at http://peltiertech.com/WordPress/installing-an-add-in-in-excel-2007/

.

Things not in the ribbon

  • There are loads of commands not in the Ribbon
  • You can’t customise the ribbon
  • So you have to add them to the Quick Access Toolbar

Eg Pivot Table Wizard; or “Send To” icon; or “Document location” (box showing the path for the current document: useful for copying and sending to others)

The Quick Access Toolbar is right at the top of the Excel 2007 screen. To edit it click on the little black arrow at its right-hand end and then select “More Commands”

.

Data: Synchronise List

This is one of the commands that is not anywhere in the ribbon! So you have to add it to the Quick Access toolbar as explained above. However when I opened an Excel 2003 spreadsheet that connects to some data in Sharepoint I got a more fundamental problem: “Excel cannot connect to the Sharepoint List”.

It was solved by this download:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=25836e52-1892-4e17-ac08-5df13cfc5295&DisplayLang=en

Actually you cannot synchronise to Sharepoint lists if you save the spreadsheet in the enw Excel 2007 format: you have to save it as an Excel 2003 file. More explanation on all this at:

http://msdn.microsoft.com/en-us/library/bb462636(v=office.11).aspx

.

Control Toolbox & Design mode

To get these in Excel 2007 you have to turn on the “developer” tab in the Ribbon. To do that:

  1. click that Big Office button
  2. Excel options
  3. Check the box beside show Developer tab in the ribbon”

.

Pivot Tables

Pivot tables seem to have changed quite a bit in Excel 2007. They no longer make spreadsheets quite so enormous or slow which is great. Unfortunately, though, a pivot table in an Excel 2007 spreadsheet cannot be opened in Excel 2003: it will just open as a read-only table.

So if a spreadsheet with pivot tables is being used in both Excel 2003 and Excel 2007, then you must make sure you save it as an Excel 2003 file (and not in the Excel 2007 file format)

.

Drawing objects not visible – ongoing problem

  • Spreadsheet created in Excel 2003 and includes some drawing objects
  • Open it in Excel 2007 and the text boxes and other drawing objects do not display
    When I click around the worksheet some of them display
    On print preview none of them show
    But when I print they all show!
  • Page Layout -> Selection Pane -> Show All: now most of them show
    Some at the edges of the worksheet are still not showing but by clicking around the worksheet they all show
    BUT they disappear again if I click Print Preview, or scroll down the page, or  temporarily go to anotehr sheet in the workbook!

Excel – some good websites

http://www.mrexcel.com/

http://www.techonthenet.com/excel/index.php

http://www.dailydoseofexcel.com/

http://www.cpearson.com/excel/mainpage.aspx

Office 2007 “ribbon” v Office 2003 menus

Moving from Excel and Word 2003 to the 2007 versions I found it very slow to do anything in the new versions. Below are some helps I found. (For details of problems transitioning Excel 2007 see my post on 10 May 2011)

1) Finding the icon in 2007 – interactive guide
You can use this guide from the web link or download it to yuour PC. It’s really helpfu: click a menu item in Excel 2003 and it shows you where the equivalent is in Excel 2007:

http://office.microsoft.com/en-us/training/guides-to-the-ribbon-use-office-2003-menus-to-learn-the-office-2007-user-interface-HA010229584.aspx?CTT=3

2) 2003 Keyboard shortcuts
In Word 2003, I used to use the keyboard shortcuts finding them quicker than icons. The 2003 keyboard shortcuts actually still work in Office 2007 (eg Alt O P to format a paragraph)

3) Office 2007 shortcuts
Office 2007 has its own keyboard shortcuts: press Alt and the ribbon-based ones are displayed.
Here’s a good list of keyboard shortcuts (eg CTRL Asterisk to select the current region)
http://www.leedr-project.com/it/access/excel2007-shortcuts.html#ctrl

Also worth knowing … CTRL Shift ! for number format with 2 decimals; or CTRL Shift ( to unhide hidden rows

3) Install the Excel 2003 menus into Excel 2007
free download:
http://download.cnet.com/Classic-Menu-for-Excel-2007/3000-2077_4-10637173.html
Or you can pay for something similar:
http://www.mrexcel.com/classicexcelmenu.html

and if you really don’t like that ribbon …
try CTRL F1


Categories

Archives