Others in Excel - Page 2 of 3

Index and match on multiple columns in Excel

This tutorial shows how to calculate Index and match on multiple columns in Excel using the example below; Formula {=INDEX(range1,MATCH(1,MMULT(–(range2=critera), TRANSPOSE(COLUMN(range2)^0)),0))} Explanation To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(–(names=G4), TRANSPOSE(COLUMN(names)^0)),0))} where …

Count cells that do not contain many strings in Excel

This tutorial shows how to Count cells that do not contain many strings in Excel using the example below; Formula {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} Explanation To count cells that do not contain many different strings, you can use a rather complex formula based on the MMULT function. In the example shown, the formula in F5 is: {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE …

Count cells less than in Excel

This tutorial shows how to Count cells less than in Excel using the example below; Formula =COUNTIF(range,”<X”) Explanation To count the number of cells that contain values less than a particular number, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells that contain numbers, …

Count cells that contain errors in Excel

This tutorial shows how to Count cells that contain errors in Excel using the example below; Formula =SUMPRODUCT(–ISERR(range)) Explanation To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function. In the example shown, E5 cell contains this formula: =SUMPRODUCT(–ISERROR(B5:B9)) How this formula works SUMPRODUCT accepts one or more arrays, multiplies …

Count cells that do not contain errors in Excel

This tutorial shows how to Count cells that do not contain errors in Excel using the example below; Formula =SUMPRODUCT(–NOT(ISERR(range))) Explanation To count the number of cells that contain errors, you can use the ISERR and NOT functions, wrapped in the SUMPRODUCT function. In the example, the active cell contains this formula: =SUMPRODUCT(–NOT(ISERR(B4:B8))) How this …

Subtotal by color in Excel

This tutorial shows how to Subtotal by color in Excel using the example below; If you need to subtotal numbers by color, you can easily do so with the SUMIF function. Formula =SUMIF(color_range,criteria,number_range) Explanation In the example shown, the formula in G5 is: =SUMIF($B$4:$B$11,F5,$D$4:$D$11) How this formula works The SUMIF function takes three arguments: range, …

Subtotal by invoice number in Excel

This tutorial shows how to Subtotal by invoice number in Excel using the example below; Formula =IF(COUNTIF(range,criteria)=1,SUMIF(range,criteria,sumrange,””) Explanation To subtotal values by invoice number, you can use a formula based on COUNTIF and SUMIF. In the example shown, the formula in E5 is: =IF(COUNTIF($B$5:B5,B5)=1,SUMIF($B:$B,B5,$D:$D),””) How this formula works This formula uses COUNTIF with an expanding range to …

Subtotal invoices by age in Excel

This tutorial shows how to Subtotal invoices by age in Excel using the example below; Formula =SUMIF(age,criteria,amount) Explanation If you need to subtotal invoice amounts by age, you can easily do so with the SUMIF function. In the example shown, the formula in G5 is: =SUMIF(age,G5,amount) Where age is a named range for the range …

Sum by group in Excel

This tutorial shows how to Sum by group in Excel using the example below; Formula =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Explanation To subtotal data by group or label, directly in a table, you can use a formula based on the SUMIF function. In the example shown, the formula in D5 is: =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Note: data must be sorted by the …

3D SUMIF for multiple worksheets in Excel

This tutorial shows how to 3D SUMIF for multiple worksheets in Excel using the example below; Formula =SUMPRODUCT(SUMIF(INDIRECT (“‘”&sheets&”‘!”&”range”),criteria, INDIRECT(“‘”&sheets&”‘!”&”sumrange”))) Explanation To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like …

44 Practical Excel IF function Examples

This chapter takes you through 44 IF function Questions and Answers in Excel. EXCEL IF function tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE Here are practical examples of IF function test in Excel: Question 1: In Microsoft Excel, I’m trying to use the IF …

Excel Operators

Excel Operators are simply functions used to perform/calculate Mathematical Operations. Although the Excel Operators are not, strictly speaking, Excel Functions, they are frequently used with Excel Functions and as a part of Excel Formulas. Therefore we have provided a brief explanation of Excel Operators in the following page: Excel Operators Excel Mathematical Operators – used to …

Excel Default Templates

Book.xltx and Sheet.xltx are two special templates you can create and add to the XLStart folder. As a result, Excel uses Book.xltx as the basis for new workbooks and Sheet.xltx as the basis for new worksheets. Book.xltx To create Book.xltx, execute the following steps. 1. Create a workbook. For example, change the row height of all rows on the …

Mark Workbook as Final in Excel

To help prevent changes to a final version of an excel file, you Mark a workbook as final. Only use this feature to discourage editing. Users can still edit the workbook. Here are steps to Mark a workbook as final below: 1. Open a workbook. 2. On the File tab, click Info. 3. Click Protect Workbook, Mark as …

Split Cell Content Using Text to Columns in Excel

To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full names into last and first names. 1. Select the range with full names. 2. On the Data tab, in the Data Tools group, click Text to Columns. …

How to Delete Cells, Row and Rows in Excel

To quickly delete a row in Excel, select a row and use the shortcut key CTRL – (minus sign). To quickly delete multiple rows, select multiple rows and use the same shortcut. Delete Row To delete a row in Excel, execute the following steps. 1. Select a row. 2. Right click, and then click Delete. Result: Note: …

How to Insert Cells, Row and Rows in Excel

To quickly insert a row in Excel, select a row and use the shortcut keys CTRL SHIFT +. To quickly insert multiple rows, select multiple rows and use the same shortcut. Insert Row To insert a row in Excel, execute the following steps. 1. Select a row. 2. Right click, and then click Insert. Result: Note: instead of …

Spell Check in Excel

Learn how to check the spelling of text in Excel. You can also add words to your custom dictionary or AutoCorrect list. 1. Open a worksheet with some spelling errors. 2. On the Review tab, in the Proofing group, click Spelling. 3. Select a suggestion and click Change to correct the misspelling in cell A2. 4. Click Change …

Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’

Formula auditing in Excel allows you find and correct errors in formulas. Also, displays the relationship between formulas and cells using Trace Precedents, Remove Arrows, Trace Dependents, Show Formulas, Error Checking and Evaluate Formula. The example below helps you master Formula Auditing quickly and easily. Trace Precedents You have to pay $96.00. To show arrows that indicate which cells are used …

Find Most Frequently Occurring Word in Excel Worksheet

Example of how to find the most frequently occurring word in an Excel Worksheet. You can use the MODE function to find the most frequently occurring number. However, the MODE function only works with numbers. You can use the COUNTIF function to count the number of occurrences of each word. However, we are looking for a single formula that returns the most …

Currency vs Accounting Format in Excel

The Currency format and the Accounting format in Excel are very similar to each other. This example shows the difference. 1. Enter the following values. 2. The Currency format places the dollar sign right next to the number. 3. The Accounting format aligns the dollar signs at the left edge of the cell and displays a dash for zero values. Note: …

Delete Blank Rows at Once in Excel

When you have empty rows cluttering your spreadsheet and you need to tighten it up, easy and fast? Removing blank rows is the first thing you should do! This example teaches you how to delete blank rows or rows that contain blank cells. 1. On the Home tab, in the Editing group, click Find & Select. 2. …

Freeze and Unfreeze Panes in Excel

If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet. Freeze Top Row To freeze the top row, execute the following steps. 1. On the View tab, in the Window group, click …

How to Create Calendar in Excel

This example describes how to create a calendar in Excel (2019 calendar, 2020 calendar, etc). If you are in a hurry. Note: the instructions below do not teach you how to format the worksheet. We assume that you know how to change font sizes, font styles, insert rows and columns, add borders, change background colors, etc. This is what …

AutoRecover file that was never saved in Excel

Excel periodically saves a copy of your Excel file. Learn how to recover a file that was never saved and how to recover a file that has been saved at least once. If Excel crashes, it displays the Document Recovery Pane, the first time you open Excel again. This is a quick way to recover the …

Zoom Worksheet in Excel

Use the buttons on the View tab to zoom to a specific percentage and to zoom to a selection. In most cases, you can use the minus and plus symbols in the status bar to quickly zoom the document. 1. To quickly zoom the document, use the minus and plus symbols in the status bar. To zoom …

Hide and Unhide Columns or Rows in Excel

Only show just the data that you need to see or print by hiding columns or rows in Excel. Sometimes it can be useful to hide columns or rows. 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, …

AutoFit Column Width, AutoFit Row Height in Excel

You probably know how to change the width of a column in Excel, but do you know how to automatically fit the widest entry in a column? AutoFit Column Width The default width of a column is 64 pixels. 1. You can change the width of a column by clicking and dragging the right border of the column …

Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel

Paste Special Transpose Transpose Function Use the ‘Paste Special Transpose’ option to switch rows to columns or columns to rows in Excel. You can also use the TRANSPOSE function. Paste Special Transpose To transpose data, execute the following steps. 1. Select the range A1:C1. 2. Right click, and then click Copy. 3. Select cell E2. 4. Right …

Lock Cells in a Worksheet Excel

Lock specific areas of a worksheet 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 …

Automatically fill series of cells in Excel using AutoFill

This lesson illustrates a quick and easy way to enter data in Excel using the fill handle. The fill handle is used to automatically fill data in series in a worksheet e.g serial Number, Days of the Week, Months of the year, Date and Time, etc. 1. For example, enter the value 10 into cell …

Flash Fill in Excel

Use flash fill in Excel 2013 or later to automatically extract or combine data. Join 1. For example, use flash fill to join the last names in column A and the first names in column B to create email addresses. 2. First, tell Excel what you want to do by entering a correct email address in cell C1. 3. …

With vs Without Array Formula in Excel

Array Formulas: Without Array Formula, With Array Formula and F9 Key This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell. With Array Formula We don’t need to store the range in column D. Excel can store this range in its memory. A range stored in Excel’s memory is called …

Common Errors in Excel

It could be challenging sometimes working in excel and you find ##### error, #NAME? error , #VALUE! error , #DIV/0! error and #REF! error on your worksheet. This chapter teaches you how to deal with some common formula errors in Excel. ##### error When your cell contains this error code, the column isn’t wide enough to display the value. 1. Click …

Cell References: Relative, Absolute and Mixed Referencing Examples

Cell references in Excel are very important. Building a structure or a template in excel using formula one needs to understand the difference between relative, absolute and mixed reference. Relative Reference To identify relative referencing, it is simply the ‘cell name’ as illustrated below. N/B: Cell Name comprises of a column label and a row number of …

Advanced Number Formats in Excel

Excel has many built-in formats. You can select standard number formats such as General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, Text on the home tab of the ribbon using the Number Format menu. If you cannot find the right format, you can create a custom number format. N/B: A custom number …

Find, Select, Replace and Go To Special in Excel

Detailed steps on how to use Excel Find, Select, Replace & Go To Features You can use Excel’s Find and Replace feature to quickly find specific text and replace it with other text. You can use Excel’s Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc. Find To quickly find …

Using Existing Templates in Excel

Templates are predefined layouts, patterns, structures built that can be reused. Microsoft office Excel comes with multiple in built Templates. If you often use the same layout or data, instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates available, waiting to be used. Existing Templates …

How to set or clear a print area in Excel Worksheet

Quick Tips On How To Set Multiple Print Areas in Excel A print area is one or more ranges of cells that you designate to print when you don’t want to print the entire worksheet. If you print a specific selection on a worksheet frequently, you can define a print area that includes just that selection. …

Lookup and Reference Examples

Data Analysis Examples

Data Validation Examples