Finding differences between two columns (Excel, Libre Office)

=IF(ISERROR(MATCH(A2,$B$2:$B$2499,0)),A2,"")

Excel - convert multiple text cells to values

To use the Paste Special command, follow these steps:

1. In any blank cell, type the value 1.
2. Select the cell in which you typed 1, and then click Copy on the Edit menu.
3. Select the cells with the values that you want to convert to numbers.
4. On the Edit menu, click Paste Special.
5. Under Operation, click Multiply. Under Paste, click Values, and then click OK.

Excel - moving to the next line in a cell

As opposed to moving to next cell!

The solution is Alt and Enter

Excel - countif greater than a value

=COUNTIF(AC2:AC9480,">31")

Excel - month and weekday from date

For month (or day), formula is =MONTH (A1) [or =DAY(A1)]

For weekday name, the formula is =WEEKDAY(A1) then use custom format of DDD to show the day name

Excel - fill in blank cells in row area in pivot tables

From http://www.mrexcel.com/articles/fill-pivot-blank-cells.php

Trick #1. Selecting all of the blank cells in that range.

• Hit Ctrl+G, Alt+S, K and then enter. huh?
• Ctrl G brings up the GoTo dialog
• Alt+S will pick the "Special" button from the dialog box
• The Goto-Special dialog is an awesome thing that few know about. Hit "k" to pick "blanks". Hit enter or click OK and you will now have selected just all of the blank cells in the pivot table outline columns. These are all of the cells which you want to fill in.

Trick #2.

Hit the equals key. Hit the Up arrow. Hold down Ctrl and hit enter. Hitting equals and the up arrow says, "I want this cell to be just like the cell above me." Holding down Ctrl when you hit enter says, "Enter this same formula in every selected cell, which, thanks to Trick #1 is all of the blank cells which we wanted to fill in.

Trick #3.

You now need to change all of those formulas to values. Select all of the cells in A3:B999 again, not just the blanks. Hit ctrl+C to copy this range. Hit alt+e then sv (enter). to Paste Special Values these formulas.

Excel: Convert forced text to numbers

Enter the number 1 in a cell somewhere away from the range of numeric values. Copy that cell to the Clipboard. Highlight the entire range of text numbers and select Paste Special from the Edit menu. Check the box titled Multiply, then click on OK. This will replace the value of each cell in the range with each value multiplied by 1.
If the cell held a text number, it will be converted to an actual number. Cells containing non numeric text will not be changed. Note that you can use the same technique to multiply, divide, add, or subtract the values in the target range by the value in the clipboard. For example, you can use this method to raise or lower a list of prices by 10 percent.

Excel: Row based page breaks

Suppose you want page breaks every 40 lines. Select the first few cells in the 40th line and change the background color, so it will stand out even when reduced in size. From the File menu, select Print Preview and note where your 40th line falls on the page. Click the Setup button and increase the bottom margin by an inch or two, then click OK. Note where the marked line falls. Go into Setup as many times as needed, adjusting the bottom margin until the marked line is the last line on the page. Click the Page Break Preview button to view your worksheet with all page breaks marked and scan through it to verify that the data is laid out as you wanted.

Excel: Highlight formula cells

First, select the entire range of cells and apply the format you want to use for cells without formulas. With the range still selected, choose Go To from the Edit menu and click on the Special button. In the Go To Special dialog, check the Formulas box, and click on OK. All cells containing formulas will be highlighted, regardless of whether they form a continuous area. Right-click on any one of them, choose Format Cells, and apply the desired formatting. You must repeat this process if you change the contents of the cells.

Excel: Find unique rows

Problem: Each row contains info in a consistent format and layout for a record in a database, with a unique ID in the first column.

Resolution: Assuming the data starts in cell A1, click in that cell and choose Format | Conditional Formatting from the menu. Change the drop-down to Formula is. Enter the formula =COUNTIF(A:A,A2)=1. Click the Format button, click the Pattern tab, and select a color, say, green. Click OK, OK. Now click the Format Painter button on the toolbar and highlight all the rest of the data in column A. Any ID that appears only once will now be green.

In Excel 2007, highlight all the cells in the unique ID column and click the big Conditional Formatting button on the ribbon. Select Highlight Cells Rules and choose Duplicate Values. In the little window that appears, change Duplicate to Unique and select the desired formatting.

Excel: Fill functions

CUSTOM LISTS
As with the months of the year and days of the week, you can create your own custom fill lists. Click the Office button and choose Excel Options | Popular | Edit Custom Lists to display the Custom List dialog. You can copy a list from a range on the worksheet or click New List and type a list of entries in the aptly named List entries box, one per line. Click Add to add the list and OK twice to finish. Now type any entry from your list in a cell and drag the fill handle to create the sequence.

MORE-COMPLEX SERIES
To create a nonlinear series, type the first value in the series, right-click, and drag on the fill handle. When the Context menu appears, select Series to open the Series dialog. Here you specify a Growth series, for example. To test this, type number 1 in a cell, right-click it and drag, choose Series | Growth and set the step value to 2, and click OK. This creates a sequence in which each number is followed by itself multiplied by two.

DROP-DOWN EASE
A drop-down list is handy when you have a set of options you want your user to choose from—and it conveniently averts typos. On a new sheet, type a list of entries, select it, and type a name for the list in the Names box on the left of the Formula bar. On a new worksheet, select the cells in which the data is to appear. Click the Data tab on the Ribbon and choose Data Validation. From the Settings tab choose Allow | List and in the Source box type = and the name you gave to the selected list (for example, =employees). Click OK. Now when you click a cell you can select the cell entry from the drop-down list.

FORMS
The Form button is not on the Excel 2007 Ribbon, but you can add it to the Quick Launch bar. Click the Office button, choose Excel Options | Customize, and in the Choose commands from list pick Commands Not in the Ribbon. Locate and select the Form command. Click Add and then OK. To display the form, click in a table of data and click the Form button. You can now enter data into your table using the Form dialog, which is quicker and more convenient than navigating around a very large table.

Excel: Enter date and time formats

To set up a worksheet so that you can enter dates as MMDDYYYY with no slashes between, first click the column header for the column that will contain the dates. Now right-click the column header and select Format Cells from the pop-up menu. On the Number tab choose Custom and enter the following custom format in the Type box: 00\/00\/0000. The forward slash has special meaning in a custom format, so you must precede it with a backslash to tell Excel that you want the literal character, not its special meaning.
Now insert a column just after this column to hold the actual date for use in calculations. Let's say the eight-digit dates were in column A, starting in cell A2. Then you would enter this formula in cell B2 and copy it down the whole column: =DATE(MOD(A2, 10000), TRUNC(A2 / 1000000), MOD(TRUNC(A2 / 10000), 100)). The MOD function returns the remainder when dividing its first argument by its second. Thus the first sub-expression returns the last four digits of your number—the year. For the month, we divide by 1,000,000 and use the TRUNC function to throw away any fractional part. And for the day, we do a combination of the two.
To enter HH:MM time values without the colon, you'll simply use a different custom format. The format 0\:00 will give one- or two-digit hours, while the format 00:\00 will always give you two-digit hours, left-padding with a zero if necessary. A formula like this one would convert the typed value into an actual time value for use in calculations: =TIME(TRUNC(A2/100),MOD(A2,100),0).

Excel: Sum cells based on colour

'Sums cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

 Dim rCell As Range

 Dim iCol As Integer

 Dim vResult



 iCol = rColor.Interior.ColorIndex



  For Each rCell In rSumRange

   If rCell.Interior.ColorIndex = iCol Then

    vResult = WorksheetFunction.Sum(rCell) + vResult

   End If

  Next rCell



 SumColor = vResult

End Function

Excel: Return a blank value

=IF(A1=0,"",1)

Excel - extract month from date

Lets say that cell A1 contains the date July 21, 2004 (entered as 21/7/2004 or 7/21/2004 depending on your date system). You want to extract the year (i.e. 2004) from that date and put it in cell C1. Simply enter the formula =YEAR(A1) in cell C1. Same thing for month and day. Use =MONTH(A1) or =DAY(A1).Then reformat as text

Excel: Prevent VLOOKUP from showing N/A

=IF(ISNA(VLOOKUP($A12,'MIS Data'!$A$2:$AK$74,21,FALSE)),0,VLOOKUP($A12,'MIS Data'!$A$2:$AK$74,21,FALSE))

Excel - Remove all named ranges in a sheet

1. Copy the code. 
2.  

   Option Explicit 
    
   Sub RemNamedRanges() 
        
       Dim nm              As Name 
        
       On Error Resume Next 
       For Each nm In ActiveWorkbook.Names 
           nm.Delete 
       Next 
       On Error Goto 0 
        
   End Sub 

3. Open any workbook.
4. Hit Alt+F11 to open the Visual Basic Editor (VBE).
5. On the left, choose a file.
6. From the menu, choose Insert-Module.
7. Paste the code into the code window at right.
8. Hit the save diskette and close the VBE.
9. Run the macro by going to Tools-Macro-Macros and double-click RemNamedRanges.

Excel - Hide N/As on VLOOKUP fails

=IF(ISERROR(), "", )

Excel - remove all hyperlinks

Select all the cells (ctrl-A (twice in xl2003))
hit alt-f11 (to get to the VBE, where macros live)
hit ctrl-g (to see the immediate window)

type this and hit enter:

selection.hyperlinks.delete

Close the VBE and back to excel to see if it worked.

Excel: Hide error values

Format text in cells that contain errors so that they don't show

1. Select the range of cells that contain the error value.
2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.The Conditional Formatting Rules Manager dialog box is displayed.
3. Click New rule.The New Formatting Rule dialog box is displayed.
4. Under Select a Rule Type, click Use a formula to determine which cells to format.
5. Under Edit the Rule Description, in the Format values where this formula is true list box, enter the following formula: =ISERROR(reference)
   Where reference is a relative reference to the cell that contains the error value.
6. Click Format, and then click the Font tab.
   
7. In the Color box, select white.

Display a dash, #N/A, or NA in place of an error value

To do this task, use the IFERROR and NA functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.  Note   Do not select the row or column headers.
   
   
   Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1 2 3

A Data 10 0 Formula Description (Result) =A2/A3 Results in an error (#DIV/0) =IFERROR(A2/A3,"NA") Returns NA when the value is an error =IFERROR(A2/A3,"-") Returns a dash when the value is an error =IFERROR(A2/A3,NA()) Returns #N/A when the value is an error

Hide error values in a PivotTable report

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable Options group, click the arrow next to Options, and then click Options.
3. Click the Layout & Format tab, and then do one or more of the following: Change error display   Select the For error values, show check box under Format. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.
   Change empty cell display   Select the For empty cells, show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.
   

Hide error indicators in cells

If a cell contains a formula that breaks one of the rules, a triangle appears in the top-left corner of the cell. You can prevent these indicators from being displayed.

---

Cell with a formula problem

---

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
2. Under Error Checking, clear the Enable background error checking check box.