Quick Reference - MS Excel – Frequently Used References



Options - Common Settings
  • Disable the Paste Options dialog that shows up when you paste something:
    Options > Advanced > Cut, Copy, and Paste section, clear the Show Paste Options Buttons check box.
Page Setup for Multiple Pages
[Top]
Set Printing Preferences for Multiple Worksheets in One Workbook
To set printing preferences (i.e. headers, footers, etc.) for multiple worksheets within a workbook to print as one finished document:
  1. Select the sheet you already costomized according to your preferences/needs
  2. Right-click on its tab and "Select All Sheets" -or- Select the sheets individually holding (CTRL) key
    NOTE: ⇒ the "active" sheet MUST be the one you customize ⇐
  3. Open the page setup dialog… and… ⇒ PRESS ENTER ONLY (The dialog will close and the page setup will be the same for all selected sheets)
Note: the following options are not available using this method: Print Area, Rows to repeat at top, Columns to repeat at left
Deactivate Hyperlinks
[Top]
For Hyperlinks in Excel, you can turn off automatic hyperlink creation in AutoCorrect Options > Auto Format as You Type. For hyperlinks already created, right click and select "Remove hyperlink". For multiple hyperlinks, use the following:
  1. Type the number 1 in a blank cell, and right-click the cell.
  2. Click Copy on the shortcut menu.
  3. While pressing CTRL, select each hyperlink you want to deactivate.
  4. Click Paste Special on the Edit menu.
  5. Under Operation, click Multiply and then click OK.
Note: this also works to restore values to their base levels (such as when numbers are copied into a cell as text).
Conditional Formatting
[Top]

Conditional Formatting Priority: Conditional Formatting rules are applied bottom to top. The highest rule on the list (top) will take precedence over any rules beneath it. The result is that if several rules may apply to a particular condition, the top-most rule will be the last applied (visible). This is convenient to use with banding, where conditional format color-coding may still applied over the banding (locate the banding formats at the bottom of the list).
Note: Make sure the range is properly adjusted to include the entire area the format is intended to be applied to (e.g. =$C, or =$A$3:$F$1106)

Conditional Formatting For a Specific Character:

If a cell contains a particular character or string; For example: for the character "@", if a cell contains "bob" there would be no format, but if it contained "bob@abc.com" finding the "@" character, the format would be applied as follows:
  • If you're looking at starting the format range at A1, you could use a formula like: =countif(a1,"*@*")>0 or =isnumber(search("@",a1))
  • If you ever wanted to look for J (not lower case j), you could use: =isnumber(find("J",a1))
  • Both =countif() and =search() are not case sensitive. =Find() is case sensitive.
Note: start Conditional Formatting in first cell of range, the "a1" should be replaced with the first cell of the range.

Conditional Formatting - Highlight One Cell Based Upon the Value in Another Cell:

In this example, the conditional format would be applied to one cell (e.g. A1) based upon the contents in another cell (e.g. B1). The applied range would be all of column A, formatted based upon the contents of column B (row-by-row). (Note: set the "Applies to" range to "=$A:$A" for the entire column)
  • The test data is located in column "B"; (This could be a variable amount, or a text string such as: "Test")
  • Use the formula for "A" column =$B1=A1 (or text string "Test" - e.g. =$B1-"Test"), with the target cell listed in the "Applies to" range (e.g. "=$A:$A")
  • This compares every "A" cell to the corresponding "B" cell located in the same row, and highlights the "A" cell when the formula is true.
  • Another Example would be "=A1=$B$1" to compare every "A" cell to a fixed value in B1 (use with other operators such as <=, >, etc., Example: "=A1>=$B1")
Note: start Conditional Formatting in first cell of range, the "a1" should be replaced with the first cell of the range.

Conditional Formatting, Highlight a Row (simple):
  • Highlight an entire row based upon Single Cell. (e.g. only "C1" to a fixed value or formula").
  • Select the range to format and use the Conditional Formatting formula option.
  • Enter a formula such as =$C3>10. This compares the value in C3 to the fixed value of "10".

Conditional Formatting, Highlight a Row (range) Based Upon the Value in One Cell Compared to Another Cell:

In this example, the conditional format would be applied to a row range, based upon the value in one cell (e.g. A1) compared the contents in another cell (e.g. F1). The applied range would be a specific row by columns A-E, formatted based upon the contents of column A (row-by-row). The row formatting would be based upon the value in column A as compared to the value in $F$1. (Note: in this example, set the "Applies to" range to "=$A$1:$E$999" to cover rows 1 to 999)
  • The test data is located in cell "F1"; For example, the desired string is: "Test"
  • Use the formula =$A1=$F$1, with the target cell listed in the "Applies to" range to "=$A$1:$E$999"
  • This compares every "A" cell to the set contents in the "F1" cell ($F$1)

Conditional Formatting, Compare Values Highlight: (use formula)
  • Compare to a single "fixed" cell (e.g. only "A1" to "B1, B2,.."), use fixed cell reference rules: =$A$1 compares every value in the formatted range to that one, single, cell (e.g. A1 to B1, B3, etc.).
  • Compare one range of cells to another range of cells (e.g. "A1" to "B1"), use cell range reference rules: =$A1=$B1, or =$A1<>$B1 compares every value in the formatted range to column A and the same row number (e.g. A1 to B1, A2 to B2, etc.).
  • Compare to a range of cells with a formula (e.g. the sum of a range of cells is greater than zero: =(SUM(A$2:A20)<=0) compares every value in the formatted range (i.e. A-Z, Row 1) to the sum of the column below from 2 to 20.

Conditional Formatting, Banding [Top]

The formulas for odd banding and even banding are very similar.  For the following formulas: where "Rw" is the first row number that is to be formatted, and "n" is the number of rows in each color band.   Note: "Rw" must be ≤ "n" or it just repeats.
  • For odd banding, use the formula "=MOD(ROW()-rw,n*2)+1<=n"
    [Example, banding starts on row 3, bands three rows: "=MOD(ROW()-3,3*2)+1<=3"]
  • For even banding, use the formula "=MOD(ROW()-rw,n*2)+1>n"
To use these formulas in Conditional Formatting.  Excel 2010:
  1. Select the cells that you want to format.
  2. Select Conditional Formatting, new rule, use a formula to determine which cells to format. 
  3. In this dialog, Under "Format values where this formula is true:" enter one of the formulas below
  4. Select the desired format, then click ok (or apply)
Note: Conditional Formatting Band color normally overrides the manually selected shading color, see examples in "Misc_Calcs.xls". (See conditional formatting priority above)
Separate Text Strings Based Upon a Specific Character:
[Top]
Use the following functions: LEFT(text,num_chars), MID(text,start_num,num_chars), RIGHT(text,num_chars)

Example: "name@test.com" located in cell C3

[LEFT] To get "name" use "=LEFT(C3,FIND("@",C3)-1)"

[RIGHT] To get "test.com" use =RIGHT(C3,(LEN(C3)-FIND("@",C3))) "FIND" returns the number of characters until the "@" is found, LEN is the total length of the string. You subtract the FIND length to the "@" from the total LEN length.

[MID] To get a text string from the middle of a cell, use "=MID(C3,FIND("@",C3)+1,99)". Note: The "99" is the legth of string to retreive.
For lesser values adjust as necessary (e.g. for MID(C3,FIND("@",C3)+1,3), only 3 characters are returned.

[RIGHT to LEFT Search] To search from the right side of the string to the left, with the text entry is in cell A1. In this example, the item to search for is a space (" "), enter the formula:
=IF(ISERROR(FIND(" ",A1)),A1,RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))) where @ is a character that is guaranteed not to occur in the text entries (you can use another character than @ if necessary).
With no error check, use: =RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Excel Default Sort Orders
[Top]
In an ascending sort, Microsoft Excel uses the following order. (In a descending sort, this sort order is reversed except for blank cells, which are always placed last.)
Numbers - Numbers are sorted from the smallest negative number to the largest positive number.

Alphanumeric Sort - When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last. Logical values: In logical values, FALSE is placed before TRUE. Error values: All error values are equal. Blanks: Blanks are always placed last.

Add Drop-Down Sort
[Top]
Sort and filter to the top of cells - select header range got to Home > Editing > Sort & Filter > Filter
Change formulas with relative values to absolute values
[Top]
Use the following macro:
Press Alt+F11 to go to the VB editor, select Insert/Module from the VB editor's menu bar, and copy/paste the following macro into the code window that opened up:

Sub ChangeReferencesFromRelativeToAbsolute()
  Dim Cell As Range
   For Each Cell In Selection
    If Cell.HasFormula Then
     Cell.Formula = Application.ConvertFormula(Formula:=Cell.Formula, fromReferenceStyle:=xlA1, _
         toReferenceStyle:=xlA1, toAbsolute:=xlAbsolute)
    End If
   Next
End Sub

In worksheet, select the cells that contain only the formulas you want to convert, run the macro (Alt+F8).
Note that mixed references (where only one part of the address has the $ sign) will be converted to all absolute references.  
Substitute Characters (Excel's version of formatting characters)
[Top]
Formatting characters in Excel are provided by ASCII codes that are invisible in cells. Replacing formatting characters in cells may be accomplished by creating a column next to the data in question, and using the following formula: =SUBSTITUTE(F6,CHAR(10),"|"), where cell "F6" has the wanted data, and "CHAR(10)" is the ASCII code 10 for manual line break, and "|" is the replacement character.
Other characters may be replaced in the same manner. The ASCII characters may be seen here: ASCII
Multiply Range, or How to Remove ' character in front of numbers's
[Top]
  When you want to multiply a range of numbers by a single number -or- remove unwanted formatting characters, such as those that occur when pasting numbers from a file into a spreadsheet (e.g. each number begins with the character ' and will not allow the column to be changed to a number format, or calculated)
  • Put the number "1" in an empty cell and copy it.
  • Select this range of text numbers and right click, paste special, select multiply
  • Delete the 1
View locked and unlocked cells visually
[Top]
When creating complex spreadsheets, and some cells are to be protected from user input (modification), like input labels, table titles, etc…
This can be done easily by using a conditional formatting with a specified formula as follows:
  • First, make sure all pages are unprotected (Review Tab, Changes, Protection)
  • Select the entire sheet by clicking on left upper corner
  • On the “Conditional Formatting” button and click “New Rule” (Home, Styles, Conditional Formatting)
  • Select “Use a formula to determine which cells to format”
  • Paste the following formula: 
     =CELL("protect", INDIRECT(ADDRESS(ROW(),COLUMN())))=1
  • Choose format to identify locked cells, (e.g. red background)
After all cells have been fixed, easy to remove this rule using same method.
Note: Changing the last digit in the formula to a “=0” will show the input cells (i.e. not locked cells). This is very useful to highlight “entry” fields for users.
Header or footer image
[Top]
How to insert a graphic or photo image in the header or footer in Excel 2013
  • On the Insert tab in an open worksheet, click the "Header & Footer" button in the Text section of the ribbon. This will give you a page view that shows the header and footer regions.
  • Click the section of the header or footer where you wish to add the image.
  • On the Design tab, click the Picture button in the "Header & Footer Elements" section of the ribbon.
  • A dialog box will open allowing you to browse to the location of the image.
  • Select the image that you want to use, then click the Insert button.
  • The header or footer should now contain the wording: "&[Picture]".
  • The image will show up in the print preview screen.
  • Adjust the margins to position the spreadsheet content and the image.

    • Note: when setting margins, the "Bottom" and "Top" position the spreadsheet content, while "Header" and "Footer" position the header and footer (image) content.
Find Links Used in Workbook
[Top]
How to find links to external Excel files
When you work on an Excel workbook, somtimes there are external links that ask to be updated. you will be notified of this with a dialog asking to update links when you open the file. Many of these links will have that workbook’s filename in the link with its .xl* file extension (e.g. .xls, .xlsx, .xlsm). Try to search on all occurances of the .xl partion of the file extension.
  • Press Ctrl+F to launch the Find and Replace dialog.
  • Click Options
  • In the Find what box, enter .xl
  • In the Within box, click Workbook.
  • In the Look in box, click Formulas.
  • Click Find All.
  • In the list box that is displayed, look in the Formula column for formulas that contain .xl.

    • Note: If you’re linking to another file-type source, you will have to determine the best search term to use.
Unprotect Excel 2016 Spreadsheet without Password
[Top]
How to Unprotect an Excel Spreadsheet without Password 2016
  • Change the Excel file to a ZIP file: rename the extension from .xlsx to .zip.
  • Extract the ZIP archive file
  • Navigate to ..\xl\worksheets\ - sheet1.xml, sheet2.xml, sheet3.xml etc.
  • Edit sheetx.xml files with Notepad ++
  • Search for string "<sheetProtection...>", Delelete entire line between brackets (e.g. <...>)
  • Save files and re-zip. Rename file to .xlsx extension.

    • Note: make sure the Excel file is re-constructed the same way it unzipped.
[Top]
Compare Two Sheets and Highlight Differences (Using Conditional Formatting) 
  • This requires the two worksheets to be both located in the same workbook. For this example, "Tab_New" will be compared to "Tab_Old"
  • Put the new worksheet into "Tab_New", the old worksheet into "Tab_Old"
  • Select the entire "Tab_New" worksheet (e.g. "=$1:$1048576"), in Conditional Formatting, insert a new rule (Use formula to determine which cells to format)
  • The formula is "=A1<>'Tab_Old'!A1" - Select the format to apply (e.g. format fill color)
  • Apply rule, and the cells that are different should be highlighted.