This article contains Count | Countif | Countifs | Sum | Sumifs | Count Text Occurrences | Count Logical Values | Count Blank/Nonblank Cells | Sumproduct The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria. Count To count the number of cells that […]
Use the ‘Paste Special Operations’ to quickly perform operations on a range of cells in Excel. 1. Select cell D3. 2. Right click, and then click Copy. 3. Select the range A1:B8. 4. Right click, and then click Paste Special. 5. Click Multiply. Note: you can also Divide, Add or Subtract a value. 6. Click […]
The quickest way to see the average, count, numerical count, minimum, maximum or sum of selected cells is by taking a look at the status bar. 1. Select a range of cells. 2. Look at the status bar at the bottom of your window to see the sum of these cells. 3. Right click the […]
this article contains; Paste | Values | Formulas | Formatting | Paste Special This example illustrates the various paste options in Excel. Cell B5 below contains the SUM function which calculates the sum of the range B2:B4. Furthermore, we changed the background color of this cell to yellow and added borders. Paste The Paste option […]
A dynamic named range expands automatically when you add a value to the range. 1. For example, select the range A1:A4 and name it Prices. 2. Calculate the sum. 3. When you add a value to the range, Excel does not update the sum. To expand the named range automatically when you add a value […]
In this article there are three main contents; Named Range | Named Constant | Name Manager Create a named range or a named constant and use these names in your formulas. This way you can make your formulas easier to understand. Named Range To create a named range, execute the following steps. 1. Select the […]
The percent change formula is used very often in Excel. For example, to calculate the Monthly Change and Total Change. 1a. Select cell C3 and enter the formula shown below. 1b. Select cell C3. On the Home tab, in the Number group, apply a Percentage format. 1c. Select cell C3, click on the lower right […]
Use flash fill in Excel 2013 or later to automatically extract or combine data. Extract 1. For example, use flash fill to extract the numbers in column A below. 2. First, tell Excel what you want to do by entering the value 4645 into cell B1. 3. On the Data tab, in the Data Tools […]
This example illustrates how to use the union and intersect operator (borders below for illustration only) in Excel. 1. The union operator (comma) adds two ranges. Explanation: the SUM function reduces to =SUM(C4:D8) + SUM(D7:E11), 20. 2. The intersect operator (single space) returns the intersection of two ranges. Explanation: the SUM function reduces to =SUM(D7:D8), […]
Use the ‘Paste Special Skip Blanks’ option and Excel will not overwrite existing values with blanks. 1. Select the range B1:B12. 2. Right click, and then click Copy. 3. Select cell A1. 4. Right click, and then click Paste Special. 5. Check Skip Blanks. 6. Click OK. .
Sometimes it can be useful to hide columns or rows in Excel. Hide To hide a column, execute the following steps. 1. Select a column. 2. Right click, and then click Hide. Result: Note: to hide a row, select a row, right click, and then click Hide. UnhideTo unhide a column, execute the following steps. […]
You can insert a comment in Excel to give feedback about the content of a cell. Insert Comment To insert a comment, execute the following steps. 1. Select a cell. 2. Right click, and then click Insert Comment. 3. Type your comment. Excel displays a red triangle in the upper-right corner of the cell. 4. […]
It’s easy to create all sorts of sequences in Excel. For example, the Fibonacci sequence. 1. The first two numbers in the Fibonacci sequence are 0 and 1. 2. Each subsequent number can be found by adding up the two previous numbers. 3. Click on the lower right corner of cell A3 and drag it […]
CMYK (cyan, magenta, yellow, key)The “K” in CMYK stands for “key,” a traditional word for a black printing plate. Short for Cyan Magenta Yellow and Key, CMYK is the four-color model used for printing standard colors. The image is an example of colors created when variations of the original three are mixed. CMYK is a […]
View buttons may refer to any of the following: 1. In Microsoft Office programs such as Excel, PowerPoint, and Word, the view buttons are a feature that lets you change how the presentation or document appears. In the picture is an example of how the view buttons appear in Excel, PowerPoint, and Word in the […]
Draft quality, draft view, or draft mode is a feature found on older printers where they would lower their quality and saturation for a “test version” of a printed document. This mode was used to allow the user to see what the print job would look like (while saving some ink) before printing the […]
Have you been using your printer with the setting that it came with? If your answer is a yes, then you’ve most probably been wasting a lot of ink. Your printer’s default settings are designed for best performance. This means great print quality but it also means excessive use of ink. You won’t need such […]
Mark a workbook as final in Excel to indicate that this is the final version of your workbook. Only use this feature to discourage editing. Users can still edit the workbook. 1. Open a workbook. 2. On the File tab, click Info. 3. Click Protect Workbook, Mark as Final. 4. Click OK. Result. Note: users […]
This example shows you how to make your workbook read-only. Users can still save changes by creating a new copy of the workbook. For a better protection, protect a sheet. 1. Open a workbook. 2. On the File tab, click Save As. 3. Click Browse. 4. Click on the Tools button and click General Options. […]
You can lock cells in Excel if you want to protect cells from being edited. In this example, we will lock cell A2. Before you start: by default, all cells are locked. However, locking cells has no effect until you protect the worksheet. So when you protect a worksheet, all your cells (=worksheet) will be […]
