Workbooks

A workbook is the name given to an Excel document and has the file extension (".xlsx").
A workbook contains one or more worksheets and there are 3 worksheets in the default workbook.
There are tabs at the bottom of the workbook to enable you to select which worksheet is currently displayed.
It is best to think of a workbook as a stack of worksheets all on top on each other.
Using multiple worksheets within a workbook allows you to organise your data better.
The default naming convention for workbooks is Book1, Book2, etc.
You can open as many workbooks as you like (memory allowing) but there can only ever be one Active Workbook at any one time.


 


Using the Control Buttons

When you open Excel there are actually two separate windows.
Both these windows have what are called control menus.
The icon in the top left hand corner is known as the control menu and the three buttons in the top right hand corner provide shortcuts for commands on this menu.
You can also display the control menu by pressing the left mouse button on the icon in the top left corner.


The outside window is the Title Bar or Application Window.
This window displays the name of the application and the name of the active workbook.


 

The workbook name will only have a file extension once it has been saved.
Minimise - Excel is reduced to a button on the taskbar.
Restore - Restores the application window to its original size before it was maximised or minimised.
or Maximise - Increases the size of the Excel window to fill the whole screen.
Close - Closes Excel completely.


The inside window is the Menu Bar or Workbook Window.


 

Minimise - Workbook is reduced to a small icon within Excel.
Restore - Restores the workbook window to its original size before it was maximised or minimised.
or Maximise - Workbook is increased to fill the whole application window. When the window is maximised this button changes to restore.
Close - Closes the active workbook.


Options

(General tab, Default file location) - Lets you change the directory that the (File > Open) and (File > Save As) dialog boxes default to.
(General tab, Recently used files list) - Lets you change the number of workbooks displayed at the bottom of the File menu.
(General tab, Sheets in new workbook) - Lets you change the default number of worksheets in a new workbook.
(General tab, User Name) - Lets you enter your username. This will automatically appear in the (File > Properties)(Summary tab) when you create a new workbook. It enables you to keep track of who creates the workbooks.
(Transition tab, Save Excel files as) - Lets you change the type of file that the (File > Save As) dialog box defaults to.
(View tab, Windows in task bar - Displays all your workbooks and windows as separate items on the task bar.


Tab Split Bar

Drag the split bar to the right if you want to see more sheet tabs and less horizontal scroll bar.
Double click the tab split to return it to its normal position


Right click on tab scrollling buttons to get a list of all the worksheets for quick selection


Important

If you have several workbooks open and you want to close them all quickly, hold down Shift and select (File > Close All). You will be prompted if there is any unsaved data.
You can view your workbooks on the full screen by pressing (View > Full Screen). This will maximise the amount of information that is viewed by removing all the toolbars except the menu bar. It will maximise Excel on your PC though.
If you re-name any workbook files using Windows Explorer do not forget to keep the (".xls") file extension. This extension identifies the file as being an Excel workbook.
The (Edit > Undo) command is workbook (not worksheet) specific and can be used to undo the last 16 actions.
You can also use the shortcut key (Shift + F12) to save a workbook.
You can also use the shortcut key (Ctrl + F12) to display the (File > Open) dialog box.


How many worksheets can I use ?

Using lots of smaller worksheets as opposed to one large worksheet has several advantages:
1) Your worksheets will recalculate faster.
2) Your data will be easier to manage and to update.
There is no restriction on the number of worksheets you can have in a workbook, as long as you have enough memory on your PC.


Remove the Blank Workbook

You can prevent the default (empty) workbook from opening every time you open Excel by adding a switch to your Excel shortcut.
Right mouse click on the shortcut you are using to open Excel.
Select Properties and append the following "/e" to the end of the target line.


Default Number of Worksheets

By default every new workbook contains 3 worksheets.
This can be changed by selecting (Tools > Options)(General tab, Sheets in new workbook).
When this settings is changed the new value is stored in the registry at the following location:
Note that this is version specific.

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options\DefSheets 


Tools > Options

All the following options are workbook specific and changes will be applied to all the worksheet in the workbook.
(View tab, Horizontal scroll bar) - Displays the horizontal scroll bar in your worksheets.
(View tab, Vertical scroll bar) - Displays the vertical scroll bar in your worksheets.
(View tab, Sheet tabs) - Displays sheet tabs at the bottom of a workbook so that you can manoeuvre and select multiple worksheets easily.


Different Layers

Although an Excel workbook may appear to be a single entity there are infact three layers
Data layer - This holds the worksheet data, numbers, text, formulas etc
Back Drawing layer - This holds background images such as background images gridlines etc.
Front Drawing layer - This contains graphics to highlight important pieces of information, charts, comments etc. This obviously hides any items in the drawing layer or data layer behind.


Excel Viewer

The Excel Viewer is free to distribute and allows you to open and print Excel workbooks.
You can also copy data from Excel Viewer to another application.
This is a replacement for Excel Viewer 97 and all previous Excel Viewer versions.


File Already Open

When you try to open a file that resides on a shared network drive while another user has the file open Excel will display the File in Use dialog box.
This dialog box gives you the option of opening the file as Read Only and also to be automatically notified when the file is available for editing.
SS
If the file was saved with a read-only password then you must still enter the appropritae password to open the file.
If you click Notify Excel will open the file in read-only mode but will notify you when the file becomes available for read-write access.
At that point the File Now Available dialog box will appear.
SS


Accessing and Saving Remote Files

Using the Save In drop-down list on the Save As dialog box you can save a workbook on any available network drive or at an FTP site or web folder on the internet.
The dialog box that is displayed when you choose (File > Open) contains a Look In drop-down list similar to the Save In list
You can use this list to retrieve workbooks saved on the network or on the Internet.



Custom Workbook Properties

Do they have to be named ranges ?
If the cell is deleted then the custom property is automatically removed ??
The username that appears in the (File > Properties)(Summary tab) is from the (Tools > Options)(General tab, "User Name") of the user who created the workbook.
It is possible to create and save custom properties within your workbooks (File > Properties).
This will also tell you when a workbook was created.
Excel 2000 and later has a "Detect and Repair" option on the Help menu that will replace any missing or damaged files that Excel needs in order to operate properly. You may require the installation CD.


Removing the "Ask a Question" box

Select (Tools > Customize) and press the Options tab.
Move your cursor over the menu bar in the corner and press the right mouse button.
Select or Deselect the "Show Ask a Question Box" to show or hide it.
If you are hiding the Ask a Question box, it will still display until after you close the Customize dialog box.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Next