Excel – Separating positive and negative numbers:

Problem:

One column contains mixed values of positive and negative numbers, needs to be
Separated into two columns, in the first column positive values in second column negative values.
Finally, add a formula to return running balance along the cells in a third column.

Solution:

Steps 1 - Into cell B2 enter the formula:

=IF($A2>=0,$A2,"") and then copy the formula down to all cells in the range of data of column A.

Steps 2 - Into cell C2 enter the formula: =IF($B2="",$A2,"") and again copy the formula down.

Steps 3 - Into cell D2 enter the formula: =SUM(A2:B2).

Step 4 - Into cell D3 enter the formula:=D2+SUM(A3:A3) and copy the formula down the remainder of the range.

Retrieving the value of first non-blank cell in the list:

Problem:

Retrieving the value of the first non-blank cell in Range1(A2:A7).

Solution:
Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX(A2:A7,MATCH(TRUE,A2:A7<>"""",0))}

Increasing or Decreasing the Screen Magnification percentage:

To quickly increase or decrease the screen magnification percentage:

1. Select a cell.

2. Press Ctrl, and roll the mouse wheel forward (to increase the magnification) or backward (to decrease it).

Convert Time Zones:

Sometimes we want to convert the time from one time zone to another. The easiest way is to get the GMT time and then calculate the appropriate time in the specified time zone.
Example:

EST____________GMT
12:00AM_______5:00AM
9:30PM________2:30AM
3:00AM________8:00AM
11:00 PM_______4:00AM


= A2+5/25

Calculating the no. of day/weeks/months/year between dates:

Calculating the difference between each pair of dates listed in columns
A & B.

Solution:

To calculate the difference in days, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,"d")

To calculate the difference in weeks, use the INT function as shown in the following formula:
=INT((B2-A2)/7)

To calculate the difference in months, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,"m")

To calculate the difference in years, use one of the following two solutions:
Use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,"y")

Counting unique values in a List:

Problem:

Counting the number of unique numeric values or unique data in List1, disregarding blank cells.

Solution1:

To count the number of unique values use the SUM, IF, and FREQUENCY functions as shown in the following formula:
= SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1))

Solution 2:

To count the number of unique data use the SUMPRODUCT and COUNTIF functions as shown the following formula:
=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

Create Pivot Table Report:

1. Select any cell in the source data, and press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A).

2. Press Ctrl+F3, and then type the defined Name for the source data.

3. From the Data menu, select PivotTable and PivotChart Report.

4. In Step 1 of 3, select Microsoft Excel list or database, and then click Next.

In Step 2 of 3, in the Range box, press F3 to open the Paste Name dialog box, and paste the Name of the source data as defined in step 2.


5. Click Next.

6. In Step 3 of 3, click Layout (in Excel 97, go to step 8).

7. In Excel 2002 and Excel 2003, you can skip this step. Instead, click Finish in Step 2 of 3 and then create the PivotTable report by dragging the fields from the Pivot Table Field List dialog box to the PivotTable report.

8. In the Layout dialog box, drag the Data Fields to the white Data area, and drag all other fields to the white Page area (except fields that are not going to be used in the PivotTable report), and then click OK.

9. In Step 3 of 3, click Finish. The PivotTable report is created.

10. Drag Data (in cell A5 in the screenshot) to the right of the PivotTable report to change the layout from horizontal to vertical.

11. The PivotTable report is now ready to be used. For more details on how to use the PivotTable report properly, see the other tips in this category.

Show Formulas in Excel:

To quickly show all formulas in your Excel spreadsheet hold down the CONTROL key on the keyboard and press the grave accent key ( ` ).
This key is located next to the #1 key on the top left corner of the keyboard and looks like a backwards apostrophe.
Show formulas does not change the spreadsheet, only the way it is displayed.
Show formulas has several uses:
• Makes it easy to find cells containing formulas
• It allows you to quickly read through all formulas to check for errors
• When you click on a formula, Excel outlines in color the cells referenced in the formula. This helps you to trace the data being used in a formula.
• Print spreadsheets with show formulas turned on. Doing so, will allow you to search a spreadsheet for hard to find errors.

Sum a range of cells:

If you insert a row directly above the Excel SUM function, the new row may not be included in the SUM. It may continue to sum cells A1:A4, and ignore A5. To ensure that new rows are included in the total, you can use the OFFSET function with the Excel SUM function.
1. Select cell A5.
2. Enter the following formula:
=SUM(A1:OFFSET(A5,-1,0))
3. Press the Enter key to complete the entry.
4. Insert a row above row 5
5. Type a number in cell A5, and it will be included in the total in cell A6

Labor Scheduling Software:

A computer software program that creates weekly work schedules for employees and departments. The software program inputs company parameters such as vacation, holidays, work availability, hours of operation, peak sales periods, etc.

Enter Data in Multiple Cells:

1. Select all the cells in which you want to enter the same value or formula
2. Type the value or formula in the active cell
3. Hold the Ctrl key and press Enter

Why does a function display #NAME:

The function may point to an add-in function that is not available to this Excel. Most frequently it's an Analysis ToolPak function; choose Tools > Add-Ins and check that there are checks against Analysis ToolPak.
Unlike Excel's built-in functions, Add-in functions do not translate themselves to regional language, so American add-in functions are by default unavailable on a Norwegian computer and vice versa.

Why do my Scroll bar go to row 500 when my data ends at row 50:

Excel may be remembering some data that was in row 500, but has been deleted. To go to the cell which is currently the "Last Cell" in the worksheet, hold the Ctrl key, and press the End key. If the Last Cell (cell J500 in this example) is outside the range of cells that is actually being used, you can reset the used range, so the scroll bar works correctly.
Note: If any cells contain references to the deleted cells, those references will be replaced with a #REF! error. If you have cells that are formatted, but outside the range that contains data, the formatting will be lost.
To manually reset the used range:
1. Select the last cell that contains data in the worksheet
2. To delete any unused rows:
 Move down one row from the last cell with data.
 Hold the Ctrl and Shift keys, and press the Down Arrow key
 Right-click in the selected cells, and, from the shortcut menu, choose Delete
 Select Entire Row, click OK.
3. To delete any unused columns:
 Move right one column from the last cell with data.
 Hold the Ctrl and Shift keys, and press the Right Arrow key
 Right-click in the selected cells, and, from the shortcut menu, choose Delete
 Select Entire Column, click OK.
4. Save the file. Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.

Add the Same Amount to Selected Numbers:

You may want to increase all the numbers in a range by a set amount. For example, in this price list, all the prices should be increased by $1.50. The following technique makes it easy to increase the prices, all at once.
1. In a blank cell, enter the amount of the increase. In this example, 1.50 was entered in cell D2
2. Copy the cell which contains the increase amount.
3. Select the cells which contain the amounts that you want to increase. Here, cells B2:B5 are selected.
4. On the menu bar, click Edit | Paste Special
5. Click Values, and click Add, then click OK.
6. Each of the selected numbers is automatically increased by $1.50

Extract unique records:

You can use an Excel Advanced Filter to extract a list of unique items in the database. For example, get a list of customers from an order list, or compile a list of products sold:
Note: The list must contain a heading, or the first item may be duplicated in the results.
1. Select a cell in the database.
2. From the Data menu, choose Filter, Advanced Filter.(In Excel 2007, click the Data tab on the Ribbon, then click Advanced Filter.)
3. Choose 'Copy to another location'.
4. For the List range, select the column(s) from which you want to extract the unique values.
5. Leave the Criteria Range blank.
6. Select a starting cell for the Copy to location.
7. Add a check mark to the Unique records only box.
8. Click OK.

Create Colored Shapes:

You can use Excel conditional formatting and the Wingding font to create coloured shapes in a cell. In this example, coloured shapes will appear in cells C3:C7, depending on the value in the adjacent cell in column B. If the value is less than 10, a red circle will appear, if the value is greater than 30, a green square will appear. Otherwise, a yellow diamond will appear.
1. In cell C3 enter the formula:
=IF(B3="","",IF(B3<10,"l",IF(B3>30,"n","t")))
2. Copy the formula down to cell C7
3. Format cells C3:C7 with Wingding font, and yellow font color
4. Select cells C3:C7
5. Choose Format|Conditional Formatting
6. From the first dropdown, choose Formula Is
7. For the formula, enter: =$B3<10
8. Click the Format button, and select Red as the font colour, then click OK.
9. Click the Add button, and for Condition 2, choose Format|Conditional Formatting
10. From the first dropdown, choose Formula Is
11. For the formula,enter: =$B3>30
12. Click the Format button, and select Green as the font colour, then click OK.
13. Click OK

Paste as CSV:

To prevent copied numbers from being pasted as text, you may be able to paste the data as CSV.
1. Copy the data in the other program
2. Switch to Excel
3. Select the cell where the paste will start
4. Choose Edit>Paste Special
5. Select CSV, click OK

AutoFilter for a Text in a Long String:

You can use the Custom option to filter for cells that contain specific text. However, if the text is located after the 255th character in the cell, it won't be found. Also, the long text strings don't appear in the dropdown list in the heading cell.
As a workaround, enter the search text string in a cell on the worksheet. Then add a formula to check for the text.
1. Insert a new column in the database, and in the heading cell, type the word you're searching for, e.g.: Shop
2. Enter the following formula in row 2 of the new column:
=ISNUMBER(SEARCH($B$1,A2))
3. Copy the formula down to the last row
4. Filter column B for TRUE
5. To filter for a different word, type a new string in cell B1, and reapply the filter in column B.
Note: SEARCH is not case sensitive. For a case sensitive filter, use FIND, e.g.:

Reasons for Invalid Data entries in Data Validation drop down:

Although you have created data validation dropdown arrows on some cells, users may be able to type invalid entries. The following are the most common reasons for this.
Error Alert
If the Error Alert is turned off, users will be able to type any entry, without receiving an error message. To turn the alert on:
1. Select the cell that contains a data validation list
2. Choose Data|Validation
3. On the Error Alert tab, add a check mark to the Show error alert after invalid data is entered box.
4. Click OK


Blank Cells in Source List
If the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. To turn prevent this:
1. Select the cell that contains a data validation list
2. Choose Data|Validation
3. On the Settings tab, remove the check mark from the Ignore blank box.
4. Click OK

Note: If the source list contains blank cells, and is a range address, e.g. $A$1:$A$10, it will block invalid entries with Ignore blank on or off.

Create Named List:

a. In an empty area of the workbook, type the entries you want to see in the drop-down lists. These should be one-word entries, to match the dependent list names that will be created.
b. Select the cells in the list (but not the heading).
c. Click in the Name box, to the left of the formula bar
d. Type a one-word name for the list, e.g. Produce.
e. Press the Enter key.

To apply the cell validation using the Named list,
a. Select the cells in which you want to apply Excel data validation using the Category List
From the Data menu, choose Validation.

c. From the Allow drop-down list, choose List
d. In the Source box, type an equal sign and the list name, for example: =Produce
e. Click OK.