Excel Use / Navigation

[Pages:25]Excel Use / Navigation

In this lesson we will explore some basic formatting techniques and some more advanced features in Excel that will help with locating and finding data within large spreadsheets. Formatting Tips : When looking at a large amount of data within a spreadsheet, it may be advantageous to enable a TABLE within the data set. TABLES allow for a number of beneficial filtering and sum options within the data set. Here is an example of a job list that has been manually keyed into a spreadsheet.

First, we can format the data within an entire column by clicking on the column header and selecting one of the QUICK FORMAT options in the toolbar. Highlight the entire column by clicking on the corresponding Column Letter. You may also use the hot-key combination of CTRL-SPACEBAR to select the entire column. SHIFT-SPACEBAR will highlight the entire ROW.

Once the entire column is selected, you may format all of the data in the column with the NUMBER format toolbar.

This will format all of the values with the "ACCOUNTING" format. This format is nice in the fact that is automatically suppresses the zero values, which can add to confusion when reviewing large amounts of data.

You may also select multiple columns and format them at the same time.

You may also use the Number format tool to format data that contains DATE values. Short Date : Long Date :

NAVIGATING LARGE SPREADSHEETS You may use a combination of hot-keys to navigate spreadsheets to access certain areas quickly. Instead of scrolling through the entire spreadsheet to get to the bottom of a list, use CTL ? DOWN ARROW to take you to the last line of data. (You may also use END ? DOWN ARROW as a substitute)

You may also use CTL ? UP ARROW to navigate to the top of the data set. (you may also use END ? UP ARROW)

If you have a data range that does not contain values in every row, and you use the CTRL-UP/DOWN ARROW, it will stop at the last available row BEFORE the blank cell in the selected column. This may be used to identify cells that are not filled in. If you are creating a sheet that needs values in every row, this function can be used to easily identify non-used cells.

CTRL-HOME will take you to the First Cell in a Data Range. CTRL-END will take you to the last cell in a data range. If you need to select a large section of data, use CTRL-SHIFT-ARROW KEY. This will highlight the selected data range.

With the entire range selected, you may right click and copy the data set. Access another worksheet and select PASTE ? PASTE SPECIAL

Select TRANSPOSE from the PASTE SPECIAL options. This will reformat your data that was previously in a single column. Transposing will paste all of the data in a single ROW.

Copy Paste Special ? Operations: To add values from two separate columns together, you may copy the values form a single column:

Paste Special ? Operations ? ADD. This will add the values from the two columns together.

The values in the two columns are now added together. NOTE : - the cells are replaced with the new values, and you lose the original numbers. If you wish to maintain the values in the original column, copy the data from the entire column (duplicate it) before preforming this function.

You may also subtract the values to establish an Estimated Gross Profit in this example.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download