Microsoft Excel

a few quick but important Tips

*** also see the Amazing 3D Charts

 

NOTE:  for these tips, the terminology must be understood.  Just above all the cells is a gray row with the column letters in it . . . A, B, C, etc.   Also, on the left there is a gray column with all the row numbers in it . . . 1, 2, 3, etc.  These are called the "Column Heading" (the top gray horizontal bar) and the "Row Heading" (the left gray vertical bar).

 

Maximum Number of Cells

 

The maximum amount of data that can be opened is 65,536 rows by 256 columns.  This can definitely limit some of the database files that have been sent to Sprint by customers, and Excel will simply stop of you try to import more data than this.  In general, just remember the number 65,000 since no one ever uses 256 columns  .  .  .  it is the rows that are occasionally maxed out.

 

The workaround is to separate the data into groups.  For example, if the data is on a National scale, you could separate it into one file for each State.

 

NOTE:  Microsoft Access can accept much more data than Excel.

UnDo

 

This feature has saved many disasters.  Click "Edit/Undo" to reverse the last change that you made.  You can undo multiple changes (up to 16) clicking Edit.Undo more than once. 

 

Keeping Headers Visible as You Scroll Down

 

With any Excel spreadsheet  -  you want the headers to always be seen, no matter how far down in the document you have scrolled.  Unfortunately, Excel does not do this by default.  I noticed that most of the spreadsheets around here - both Internal and Customer-bound    -     have the headers at the top only, and then as soon as you page down, you lose the headers.  If there are many columns, there is no way to tell which is which and you have to scroll back to the top to see which column is which.  The same is true for printouts.  There is an easy fix for both.

 

To show headers on every page of the soft copy :

 

1)  select the row immediately below the header row, by clicking the number in grey at the left.

2)  click "Window/Freeze Panes"

 

Now when you scroll down, the headers stay in view

 

To show headers on every page of a hardcopy (printout) :

 

1)  goto File/Page Setup  .  .  . 

2)  click the "Sheet" tab at the top

3)  in the field, "Rows to Repeat at Top", enter the rows which contain your headers.  For example, if rows 5,6, and 7 contain the headers that

you want to appear at the top of every printed page, enter :           

$5:$7   (this selects all rows, from row 5, to row 7)

 

Now when you print, each page will have the headers at the top.

 

Copying/Pasting Cells to another Worksheet and retain the Column Widths

 

Surprisingly tricky but simple to do.  You would think the copy command would also copy the width of each column, but it does not.  So you then try "Edit/Paste Special" and select "ALL" - but that does not work either.  So you try selecting ALL and Column Widths  .  .  .  but it won't let you select more than one check-circle.  Anyway, here's how - it is a simple 2-step process:

 

Step 1

Step 2

 

 

 

 

Worksheets

By default, excel opens up with a number of worksheets at the bottom (named Sheet1, Sheets2, etc.).  These are like mini-documents inside the main document, which is called a "Workbook".

Rename a Worksheet - double-click the tab at the bottom, and then type to edit the name

Change the Order of the WorkSheet Tabs - just click on one of the tabs and drag it left or right.  You will see a little black indicator arrow move with the sheet.

Hide/Unhide a Worksheet - sometimes you use a sheet just for calculations, or as a reference for other data - you can hide it by clicking Format/Sheet/Hide.  To unhide it, click Format/Sheet/Unhide . . . and click the sheet listed.

Making More Sheets Show Up by Default -  go to Tools/Options/General . . . Sheets in Workbook and increase the value

Adding a New Sheet - click "Insert/Worksheet"

Deleting a Worksheet - right-click on the tab at the bottom to bring up a popup menu, and then left-click on "Delete"

Columns and Rows

Selecting the Entire Document

Click the "Select All" button (the small upper left gray square, just to the left of the column headings).

Selecting Entire Row/s or Column/s

click the Column Heading and drag if you want to select multiple columns.  Likewise for the rows.

Deleting Entire Row/s or Column/s

click the Column Heading and drag if you want to select multiple columns.  Likewise for the rows.

Resizing Columns

Move the mouse between two cells in the column heading (it will change to a double-arrow when you have it in the correct position), and click-drag the border

Resizing all the column Widths so that all the Text in each Cell is Visible

Click the small upper left gray square to select all cells

move the mouse between two cells in the column heading, and double-click

NOTE:  this can make columns that have extensive text in their cells, too large (the columns with custome names, for example), and you can then resize that column to make it smaller.  If you need to see all the text, then turn on Word Wrapping.

Text Wrapping

Causes the text to jump down to a new line when it reaches the edge of a cell.  To turn it on:

Multiple Lines of Text within a Cell

  1. Type the first Line
  2. hit "ALT-Enter to begin a new line
  3. Type the next line of text
  4. repeat to add as many lines as you wish - all in the same cell
  5. when done with the last line, hit <Enter>
  6. DONE !!!

Create a Series of Numbers

NOTE:  these require you to drag the mouse downwards, by first click on the "drag handle", which is a tiny black box on the lower right portion of the cell.  You have to be very exact when clicking because the box is so small.

Series is repeating one number - for example, 56, 56, 56, 56 -.

Series is increasing - for example, 56, 57, 58, etc.

Series skips numbers, for example -  10, 20, 30, etc

Series of Dates, for example, March 1, 2000, until March 15th (days)

NOTE:  you can then format the cells to display the date any way you like, by clicking Format/Cells . . . click the "Number" tab and select the style you want.  You can do a succession of hours or minutes this was also.

Summing up a Column of Figures

Drag the mouse to select all the the cells in the column, while making sure that just below your selection - the cell is empty

click the summation Button (looks like a large, Egyptian "E") and the sum will show in the cell below

Math Functions

These are what makes Excel so powerful,  You can take data in other cells, and manipulate it mathematically and store the result in another cell.  You can then take several of these stored results, and manipulate them and store the result in yet another cell.  Once the math functions are in place, the result changes by merely entering new data in the beginning cells.

In addition, these functions can be copied to multiple cells by just dragging down, like we did to create the series.

  1. click the cell where the function is to be placed
  2. hit the equals key, " = "
  3. click a cell that is part of the equation
  4. hit the math function key (+, -, *, or / )  the asterisk means multiple and the slash is divide
  5. click the cell with the next number
  6. hit Enter to finalize the equation

For longer equations, parenthesis can be placed around groups of numbers.  The calculations can de quite complex if need be.

For example, if you have three numbers (5, 8, and 4) in cells B2, C2, and D2 and you need to store the sum in E2  .  .  .  simply click the E2 cell, hit =, then click the B2 cell, hit +,  click the C2 cell, hit +,  click the D2 cell,  and hit Enter,  At this point the cell will display the result of 17, but if you click the cell and look at the formula bar at the top, you will see that the cell contains the following :

=B2+C2+D2