Checking for the Presence of Specified Values Within a Range in Microsoft Excel
- Select the cell B2, and write the formula =Countif(A2:A11, A2) and press the Enter key on the keyboard.
- The Countiffunction will return 4which means that “Washington” is repeating 4 times in column A.
a<x<b, a≤x<b, a<x≤b and a≤x≤b to mean "x is between a and b", where less-than (<) means not including a or b, and less-than-or-equal (≤) means including a or b. The symbol ≤ can be entered by pressing and holding ALT while typing the digits 243 on the numeric keypad, then releasing ALT.
As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet. It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement.
Excel COUNTIFS Function
- Summary. The Excel COUNTIFS function returns the count of cells that meet one or more criteria.
- Count cells that match multiple criteria.
- The number of times criteria are met.
- =COUNTIFS (range1, criteria1, [range2], [criteria2], )
- range1 - The first range to evaulate. criteria1 - The criteria to use on range1.
- Version.
To hide Excel formulas, perform the following steps.
- Select a cell or range of cells containing the formulas you want to hide.
- Open the Format Cells dialog by doing any of the following:
- In the Format Cells dialog box, switch to the Protection tab, and select the Hidden checkbox.
- Click the OK button.
To see Excel's conditional formatting options to highlight top or bottom values, click the Conditional Formatting command on the Ribbon's Home tab. Then, point to the Top/Bottom option, to see all the available variations.
To highlight the top three values in excel, follow these steps:
- Select the range.
- Go to Home ? Conditional Formatting ?
- Here, select "format only top or bottom ranked value"
- In the drop-down, select Top.
- In the value box, write 3.
- Select the formatting of the cell for top 3 values. I have selected a green fill.
- Hit Ok.
Top 10 Most Useful Excel Formulas
- SUM, COUNT, AVERAGE. SUM allows you to sum any number of columns or rows by selecting them or typing them in, for example, =SUM(A1:A8) would sum all values in between A1 and A8 and so on.
- VLOOKUP.
- CONCATENATE.
- MAX & MIN.
- CONDITIONAL FORMATTING.
- INDEX + MATCH.
One quick and easy way to add values in Excel is to use AutoSum. Just select an empty cell directly below a column of data. Then on the Formula tab, click AutoSum > Sum.
There is a shortcut for placing absolute cell references in your formulas! When you are typing your formula, after you type a cell reference - press the F4 key. Excel automatically makes the cell reference absolute! By continuing to press F4, Excel will cycle through all of the absolute reference possibilities.
An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values. Array formulas can return either multiple results, or a single result.
The Excel SORT function sorts the contents of a column, columns (range) or array in ascending or descending order. The syntax is: =SORT(array, [sort_index], [sort_order], [by_col]) array is the range or array containing the values you want sorted. sort_index is optional and indicates the row or column to sort by.
Here are the steps to do this:
- Go to Row Label filter –> Value Filters –> Greater Than.
- In the Value Filter dialog box: Select the values you want to use for filtering. In this case, it is the Sum of Sales (if you have more items in the values area, the drop down would show all of it). Select the condition.
- Click OK.
Show Formulas
- When you select a cell, Excel shows the formula of the cell in the formula bar.
- To display all formulas, in all cells, press CTRL + ` (you can find this key above the tab key).
- Press ↓ twice.
- To hide all formulas, press CTRL + ` again.
Shortcut Keys
| = | Start a Formula. |
|---|
| Ctrl + ~ | You can toggle between displaying the values and formulas by pressing |
| F2, F9 | Pastes a formula as values. |
| Ctrl + A | Displays the Function Arguments dialog box when pressed after entering an equal sign followed by the function name. |
In Microsoft Excel, if you enter a formula that links one cell to a cell that is formatted with the Text number format, the cell that contains the link is also formatted as text. If you then edit the formula in the linked cell, the formula is displayed in the cell rather than the value that is returned by the formula.
Converting formulas to values using Excel shortcutsSelect all the cells with formulas that you want to convert. Press Ctrl + C or Ctrl + Ins to copy formulas and their results to clipboard. Press Shift + F10 and then V to paste only values back to Excel cells.
Find cells that contain formulas
- Select a cell, or a range of cells. If you select one cell, you search the whole worksheet. If you select a range, you search just that range.
- Click Home > Find & Select > Go To Special.
- Click Formulas, and if you need to, clear any of the check boxes below Formulas.
Click "Insert" in Controls Group
- Click "Insert" in Controls Group.
- Click “Insert” in the Controls group on the Developer tab in Excel.
- Select "Toggle Button"
- Select “Toggle Button” from the list of ActiveX Controls.
- Click where Button Should Appear.
To cut and paste cell content:
- Select the cell(s) you want to cut.
- Click the Cut command on the Home tab, or press Ctrl+X on your keyboard.
- Select the cells where you want to paste the content.
- Click the Paste command on the Home tab, or press Ctrl+V on your keyboard.
If you don't like this behavior, but instead want F2 to activate the Formula bar, follow these steps:
- Display the Excel Options dialog box.
- Click the Advanced option at the left of the dialog box.
- Make sure the Allow Editing Directly In Cell check box is cleared.
- Click on OK.
Create an Absolute ReferenceSelect a cell, and then type an arithmetic operator (+, -, *, or /). Select another cell, and then press the F4 key to make that cell reference absolute. You can continue to press F4 to have Excel cycle through the different reference types.
