EXCEL: Working With Timesheets

I found is great timesheet worksheet that generates a timesheet per person. Since date and time are difficult to explain, I wanted to give you the file that I found from Excel Bible written by John Walkenbach. Have fun playing with it. Click on this link to open a copy of the exvel file: http://sdrv.ms/16I5wwg

EXCEL: Working with Formulas that Manipulate Text….. Part 5

Here are some cool tricks when dealing with formuals that manipulate text.

1. You can use the & operator to combine text and formulas or other cells to make a string of text.  If I wanted to display in A 200 The Grand Total Is 122 and 122 calculated in H435, I no longer need to place the text in cell A200 and link the cell H435 in B200.
   Simplye type in A200:    =”The Grand Total Is ” & H435
   There is a space after the word Is. The value 122 is treated as text even if it is formated as currency or accounting. The result is The Grand Total Is 122
2. To display the number as currency with the string:
   Simply type in A200:   =”The Grand Total Is ” & text(H435,”$#,###.00″)
   The result is The Grand Total Is $122.00
3. You can also use anyformula rather than referencing a cell.
   Simple type =”The largest value in column M is ” & max(m:m)
4. You can also use a formula in step 2 as well.
   =”The Grand Total Is ” &  text(sum(H200:H430),”$#,###.00″) 0r
   =”The Grand Total Is “&DOLLAR(H435,2)
   The 2 represents the number of decimal places.
5. The REPT function repeats a text string any number of times.
   =REPT(“-”,30)
   This will repeat the dash (-) 30 times across a cell.
6. To remove excess spaces or nonprinting characters use the following text functions:
   TRIM: This functin removes all leading and trailing dspaces and replaces internal string of multple spaces with wsincle space.
   =TRIM(A1)
   CLEAN: This function removes all nonprinting characters from a string.  These nonprinting characters appear in the cells when the information was imported from an outside source.
   =CLEAN(A1)
7. There are times when you received text that are all in caps.  You can use the PROPER function to convert the text in proper case. But all the first letter of all the words in the cell will be in proper case. What if you want to only  have the first letter in upper case and the rest in lower case? For example, in cell A1 contains the words FIRST QUARTER. Use the LEFT function to extract the first letter and then convert it as uppercase. Then concatenate the rest of the string that will use the RIGHT function to extract all but the first letter and convert it these letters to lowercase.
   RIGHT returns a specified number of character from the end of a string.
   LEFT returns a specified number of characters from the beginning of a string. LEFT(A1,5) —> FIRST or LEFT(A1) —> F
   LEN returns the number of characters in that string.=(LEFT(A1)) & RIGHT(LOWER(A1), LEN(A1)-1) —>First quarterNotice that the LEFT function will return the value F.
   LEN(a1) retruns 13 then subtract which is 12. Then from the right count 12 and use the LOWER function —>irst quarter.
8. Here are the formulas that will separate the first name, last name, and middle initial in column B, C and D.
   In B1 for the First Name
   =LEFT(A2,FIND(” “,A2)-1)
   In C1 for the Middle Initial
   =IF(LEN(B2&D2)+2>=LEN(A2),”",MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2&D2)-2))
   In D1 for the Last Name
   =RIGHT(A2,LEN(A2)-FIND(“”*”",SUBSTITUTE(A2,”" “”,”"*”",LEN(A2)-LEN(SUBSTITUTE(A2,”" “”,”"”")))))”

   

EXCEL: Histograms…Who Knew This Method Is Useful?

Use the REPT function to create histogram directly in a worksheet. This method is useful when you need a visual summary of many values and to perform what-if analysis with the existing numbers. Use the formulas in column D to graphically draw solid squares to represent the numbers in the table:
=REPT(CHAR(162),B2/100)
This formula will draw certain number of solid squares based on the number divided by 100. If the number is 945, the formula will draw 9 solid squares.  If the number changes, the result will change.  Of course copy this formula down.  See the diagram below.

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.

 

 

EXCEL: Did You Know?….. Part 1

Using Ctrl+Enter to place information into multiple cell simultaneously!

If you need to enter the same data into multiple cells, Excel offers a fabulous shortcut.

1. Select all the cells that you need to change.
2. Enter the value, text, or formula.
3. Press Ctrl+Enter.
4. The same information is inserted into each in the selection.

Using a form for data entry!

When you have a list of information in a table, the column names do more than describe what needs to be typed in that column but can be used in a form.  If the table has many columns, it may become an inconvenience to scroll back and forth. One way is to use Freeze Pane to keep the main column(s) and row(s) from not moving. I recommend using the Form command which will become a data entry where it will display the column headings as fields for input.

1. Place in the Quick Access Bar, the Form command.
2. Type out the column headings. For example: First Name, Last Name, Address, City, State, Zip Code, and Salary.
3. Click on Insert Table and select the headings.
4. Check in the dialogue box “My table has headings”
5. A blank row appears in the table.
6. Format each empty cell under each heading to accommodate the information. For example: place the Accounting format for the Salary column.
7. Click on the Form icon from the Quick Access Bar
8. A data entry form appears.
9. Enter the information in each field and hit Enter to enter the information into the table.
10. This is  will definitely expedite entering the information.

Entering the current date or time into a cell using the keyboard!

• Current date:  Ctrl+;
• Current time:  Ctrl+Shift+;

Formatting numbers using the keyboard!

• Ctrl+Shift+~  —> General format.
• Ctrl+Shift+$   —> Currency format with two decimal places.
• Ctrl+Shift+%  —> Percentage format with no decimal places.
• Ctrl+Shift+!   —> Two decimal places, thousands separator, and a hyphen for a negative values.

 Using the keyboard to enable the Cells dialogue box!

• Ctrl+1

Using the keyboard to insert a blank or a new workbook!

• New workbook:   Ctrl+N
• New worksheet:  Shift+F11

Selecting cells without using the MOUSE!

• To start the the highlighting process, simply hit F8 and the arrow keys. Hit F8 to turn it off.
  OR
• Hold down the shift and use the arrow keys.

To change formats on multiple sheets at the same cells at the same time!

1. Activate the first worksheet.
2. Press down on the Ctrl and click on the second worksheet.
3. Notice that the workbook window’s title bar displays [Group] to remind you that you have selected a group of sheets and that you are in Group mode.
4. Apply the formats.
5. The worksheets have now been formatted at the same time.
6. Click on another sheet outside the group to ungroup the selected worksheets.

KEEP AN EYE OUT FOR PART 2!!!!!