Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get relative row numbers in a range in Excel
  • How to use Excel MMULT Function
  • How to use Excel ROWS Function
  • How to get relative column numbers in a range in Excel
  • How to get first column number in range in Excel

Data Analysis Examples

  • How to sum a total in multiple Excel tables
  • Remove Duplicates Example in Excel
  • Excel Line Chart
  • Managing Conditional Formatting Rules in Excel
  • How to Create Thermometer Chart in Excel

Data Validation Examples

  • How To Create Drop-down List in Excel
  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days
  • Excel Data validation require unique number
  • Excel Data validation must not contain

Category: Others

Excel Tutorials for beginners, Intermediates and experts.

Index and match on multiple columns in Excel

by

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 …

Continue Reading

Count cells that do not contain many strings in Excel

by

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 …

Continue Reading

Count cells less than in Excel

by

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, …

Continue Reading

Count cells that contain errors in Excel

by

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 …

Continue Reading

Count cells that do not contain errors in Excel

by

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 …

Continue Reading

Subtotal by color in Excel

by

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, …

Continue Reading

Subtotal by invoice number in Excel

by

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 …

Continue Reading

Subtotal invoices by age in Excel

by

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 …

Continue Reading

Sum by group in Excel

by

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 …

Continue Reading

3D SUMIF for multiple worksheets in Excel

by

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 …

Continue Reading

44 Practical Excel IF function Examples

by

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 …

Continue Reading

Excel Operators

by

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 …

Continue Reading

Excel Default Templates

by

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 …

Continue Reading

Mark Workbook as Final in Excel

by

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 …

Continue Reading

Split Cell Content Using Text to Columns in Excel

by

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. …

Continue Reading

How to Delete Cells, Row and Rows in Excel

by

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: …

Continue Reading

How to Insert Cells, Row and Rows in Excel

by

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 …

Continue Reading

Spell Check in Excel

by

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 …

Continue Reading

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

by

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 …

Continue Reading

Find Most Frequently Occurring Word in Excel Worksheet

by

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 …

Continue Reading

Currency vs Accounting Format in Excel

by

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: …

Continue Reading

Delete Blank Rows at Once in Excel

by

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. …

Continue Reading

Freeze and Unfreeze Panes in Excel

by

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 …

Continue Reading

How to Create Calendar in Excel

by

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 …

Continue Reading

AutoRecover file that was never saved in Excel

by

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 …

Continue Reading

Zoom Worksheet in Excel

by

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 …

Continue Reading

Hide and Unhide Columns or Rows in Excel

by

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, …

Continue Reading

AutoFit Column Width, AutoFit Row Height in Excel

by

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 …

Continue Reading

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

by

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 …

Continue Reading

Lock Cells in a Worksheet Excel

by

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 …

Continue Reading

Automatically fill series of cells in Excel using AutoFill

by

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 …

Continue Reading

Flash Fill in Excel

by

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. …

Continue Reading

With vs Without Array Formula in Excel

by

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 …

Continue Reading

Common Errors in Excel

by

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 …

Continue Reading

Cell References: Relative, Absolute and Mixed Referencing Examples

by

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 …

Continue Reading

Advanced Number Formats in Excel

by

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 …

Continue Reading

Find, Select, Replace and Go To Special in Excel

by

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 …

Continue Reading

Using Existing Templates in Excel

by

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 …

Continue Reading

How to set or clear a print area in Excel Worksheet

by

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. …

Continue Reading

How to share Excel data with Word documents and other files

by

Why do you need to share to data in another file format? Most of the time, you’ll simply need to paste static Excel data in a Word document Other times you may want to paste data and then click Linked to Worksheet Object. 1. Select the Excel data. 2. Right click, and then click Copy (or …

Continue Reading

Posts navigation

  • Previous
  • 1
  • 2
  • 3
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to get top level domain (TLD) in Excel
  • How to convert text string to array in Excel
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Number of Instances in Excel
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • Add months to date in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate next day of week in Excel
  • Display Days in month in Excel
  • PV function: Description, Usage, Syntax, Examples and Explanation
  • Calculate original loan amount in Excel
  • EFFECT function: Description, Usage, Syntax, Examples and Explanation
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles