More Advanced Excel Skills

Windowing Workbooks

To view two different workbooks at the same time:

Open both workbooks

Window menu

Compare side by side

Remember that only ONE of them is active at any given time.  You may switch between them simply by clicking back and forth, or by pressing Ctrl + F6

Windowing Worksheets

Open the workbook

Window menu

New window – a mirror image will appear

Window menu again

Compare side by side with OR

            Arrange           

            Vertical

            Window of active workbook

Remember

            Save will overwrite or replace the original file with changes

            Save As will allow you to save the file under a different name or in a different format

            Save Workspace will create a file that remembers all the workbooks you currently have open and will re-create your “workspace”.

Split Screen

You can move the cells and keep the titles by hovering on the // bar on the side until it has two arrows.  This will allow you to view distant parts of a worksheet at the same time.

Freeze panes

Select a cell – all rows above and all columns to the left will be Frozen

Go to Window and Freeze Panes

To unfreeze:  Go to Window and Unfreeze Panes

Formulas

All formulas start with =

Formulas are carried out in the “order of operation”.

            P – Parentheses                        Please

            E – Exponent                            Excuse

            M – Multiplication                      My

            D – Division                              Dear

            A – Addition                             Aunt

            S – Subtraction                          Sally

Formulas can be typed into the cell or you can Point and Click.

Lists

Create a header row and BOLD all the words

Format the header row differently than your data

Enter data to populate the list

Leave no entirely blank rows or columns in the list

Do not type leading or trailing spaces

Separate rows using cell borders, not empty rows or rows with dashes

If you are entering lots of data you can then click on any cell in your list, Go to the Data menu and Select Forms.  This will make it easier to enter the data.

Sort

Click on one cell

Choose the Data menu

Select Sort

Hiding Columns

You can hide one or more columns of your choice in order to display your data more efficiently or to prepare for printing.  To Hide one or more columns:

            Select the column you wish to hide

            Right click on one of the selected columns

            Select Hide 

To Unhide:

            Select the columns on either side of the ones which are hidden

            Right click and select Unhide