EXCEL: Working with Formulas and Functions….. Part 4

I see many people who double click in an empty to start typing either a formula or text. Why? I do not know but here is the correct approach….simply click once on a cell and type and enter.

Editing a Cell:

When you need to edit information that is in your worksheet, here are severals ways to get the cell in edit mode:

1. Select the cell and type the new information and hit ENTER.
2. Double-click the cell, which enables you to edit the cell contents directly in the cell. Where you you double click, the I-beam appears.
3. Press F2, which enables you to edit the cell content directly in the cell.  The I-beam always is located at the right of the cell.
4. Select the cell that you want to edit, and then click in the Formula bar. This enables you to edit the cell contents directly in the Formula bar.  I use this technique when the formula is very long and I can go directly to where it needs to be edited.
5. If the cell contains a formula that returns an error, Excel will display a small triangle in the upper-left corner of the cell. Activate the cell, and you will see a Smart Tag. Click the Smart Tag, and choose one of the options for correcting the error.

Did you know the concept known as RANGE INTERSECTIONS?

Range intersection is individual cells that two ranges have in common. Excel uses an intersection operator which is the space character to determine the overlapping reference in two ranges.  This is a great method when you have large a worksheet. See the below worksheet. The formula is located in A11 —> =C1:C6 A3:E3  There is a space bewteen these two ranges.  The formula returns a value of 457 since it is the intersection of these two ranges.

 

Converting Formulas toValues:

Sometines you need to use just the values that were produced by formulas.  Copying and Pasting will not work since Relative Addressing is in effect and cause the formulas to change either displaying erroneous values or blanks or errors. Therefore, you want to copy and PASTE VALUES ONLY command. In 2007/2010 when you use the Paste command on the ribbon or right click to bring up the short-cut menu to show the options.  You want to click on the icon that looks like a clipboard with the numbers 123.  Placing your mouse over that icon will display the icon tip which says Values (V).  DO NOT get confused with the keyboard command Ctrl+V which pastes the formulas.

EXCEL: Did You Know When Formatting a Worksheet You Can…..? Part 3

Using the Format Cell Dialogue Box:

• Pres Ctrl+1
• or Click on the Dialog Box Launcher icon (the square icon is found on the right side of the group name)

Changing Text Alignment:
We know the Left, Right, Center but did you know the what the following commands can do? Use the Format Cell Dialog box for the following commands.

• Fill –> Repeats the contents of the cell until the cell’s width is filled. You need to first choose from the Format Cell Dialog box the Fill option. Type in one or more characters and hit Enter. You can copy this cell to repeat the information.
• Justify –> Justifies the text to the left and right of the cell. This option is applicable only if the cell is formatted as wrapped text and uses more than one line. The last line is NOTjustified.
• Center Across Selection –> Centers the text over the selected columns. This option is useful for precisely centering a heading across a number of coulmns. Enter the information in one cell and highlight the empty cells that are to the right.  This is the NOT the  same as Merge & Center. This command does not merge the cells.
• Distributed –> Distributes the text evenly across the selected column. This is known as Forced Justified. The last line IS justified.

Merging Infomation:

• Merge Across –> When a multiple rows are selected including selecting emptys cells to the right of the information, this command creates merges cells — one for each row but DOES NOT Center.
• Merge & Center –> Only performs on one row with slected empty cells to the right of the information.  This command merges and centers. Merge & Center works on one row at a time.
• Merge Cells –> Merges the selected cells without applying the Center attributes. All the cells that are merged now belong to the first cell that was highlighted.
• Unmerge Cells –> Unmerges the slected cells.

Hiding Information Without Using the Hide/Unhide commnands:

• To hide the information, change the font color to match the color of the cell.  The content of the cell is still visible on the Formula Bar! Also some printers may override this setting and may still print out the information.

EXCEL: Did You Know in a Table You Can…..? Part 2

Insert Table converts the information typed in rows and columns into a structured table.  In Excel a table is a rectangular range of structured data. Each row in the table corresponds to a single record.  How do you know what makes up a table?  You simply click in a cell that has data and click CTRL+A and it will select the entire table.  You will notice that the table is surrounded by white empty cells.  Therefore, there can exist many tables in one sheet and Excel will recognize it by the white empty cells.

Selecting Parts of a Table:

When you move your mouse around in a table, you may notice that the pointer changes shapes. These shapes help you to select various parts of the table.

• To Select an Entire Column:
  Move the mouse to the top of any cell in the header row and the mouse pointer changes to a down-pointing arrow. Click or CTRL+Spacebar to select the data in the table column . The header name is not chosen.
• To Select an Entire Row:
  Move the mouse to the left of any cell in Column A of a row and the mouse pointer changes to a right-pointing arrow. Click or Shift+ Spacebar to select the entire table row.
• To Select an Entire Table:
  Move the mouse to the upper-left part of the upper-left cell. When the mouse pointer turns into a diagonal arrow, Click to select entire table. You can also click CRTL+A.

Excel Remembers:

Excel remembers the format and formula so when you enter a new row within the table, it will apply that format or formula to that cell. This is also true for cell protection, data validation, conditional formatting, and so on. If a chart is applied to the table, the new information is automatically extended to the chart.

Removing Duplicate Rows in a Table:

When you choose the Remove Duplicate command from the Table Tools, Excel will delete a row ONLY if the content of every column is duplicated. In some situations, you may not care about matching some columns, so you would delselect those columns in the Remove Duplicates dialog box. When duplicate rows are found, the first row is kept and subsequent duplicate rows are deleted.

Filtered Data: 

When you copy data from a filtered table, only the visable data is copied. The hidden rows are not copied. When you paste the information, the paste act as Paste Values Only.  If you choose to the information with formulas, Relative Addressing goes into affect and you need to place Absolute in the formulas before you copy and paste.