Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Managing Conditional Formatting Rules in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to Create Column Chart in Excel
  • How to add Trendline to a chart in Excel
  • Filter Data Based on Date in Excel

References

  • Convert text string to valid reference in Excel using Indirect function
  • Two-way lookup with VLOOKUP in Excel
  • Count rows that contain specific values in Excel
  • To count total rows in a range in Excel
  • How to reference named range different sheet in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation date in specific year
  • Data validation must not exist in list
  • Excel Data validation no punctuation
  • Excel Data validation specific characters only

Tag: OFFSET function

How to use Excel OFFSET function

by

This Excel tutorial explains how to use the OFFSET function with syntax and examples. Excel OFFSET function Description The Microsoft Excel OFFSET function returns a reference to a range that is offset a number of rows and columns from another range or cell. OFFSET function is a built-in function in Excel that is categorized as a Lookup/Reference …

Continue Reading

Count visible rows only with criteria in Excel

by

This tutorial shows how to Count visible rows only with criteria in Excel using the example below; Formula =SUMPRODUCT((range=criteria)*(SUBTOTAL(3,OFFSET(range,rows,0,1)))) Explanation To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET. The problem The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden …

Continue Reading

Sum every n rows in Excel

by

This tutorial shows how to Sum every n rows in Excel using the example below; Formula =SUM(OFFSET(A1,(ROW()-offset)*n,0,n,1)) Explanation To sum every n rows, you can use a formula based on the OFFSET and SUM functions. In the example show, the formula in F4 is: =SUM(OFFSET($C$3,(ROW()-4)*5,0,5,1)) How this formula works In this example, there are 5 …

Continue Reading

Sum through n months in Excel

by

This tutorial shows how to Sum through n months in Excel using the example below; Formula =SUM(OFFSET(start,0,0,N,1)) Explanation To sum a set of monthly data through n number of months, you can use a formula based on the SUM and OFFSET functions. In the example shown, the formula in G6 is: =SUM(OFFSET(C5,0,0,G5,1)) How this formula …

Continue Reading

Average last 5 values in Excel

by

This tutorial shows how to work Average last 5 values in Excel using the example below; Formula =AVERAGE(OFFSET(A1,COUNT(A:A),0,-N)) Explanation To average the last 5 data points, you can use the AVERAGE function together with the COUNT and OFFSET functions. You can use this approach to average the last N data points: last 3 days, last 6 measurements, …

Continue Reading

Average last 5 values in columns in Excel

by

This tutorial shows how to work Average last 5 values in columns in Excel using the example below; Formula =AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N)) Explanation To average the last 5 data values in a range of columns, you can use the AVERAGE function together with the COUNT and OFFSET functions. In the example shown, the formula in F6 is: =AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5)) How this …

Continue Reading

Offset in Excel

by

The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. OFFSET function Syntax; OFFSET(reference, rows, cols, [height], [width]) 1. The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The …

Continue Reading

How to create dynamic named range with OFFSET in Excel

by

One way to create a dynamic named range with a formula is to use the OFFSET function together with the COUNTA function. Dynamic ranges are also known as expanding ranges – they automatically expand and contract  to accommodate new or deleted data. Note: OFFSET is a volatile function, which means it recalculates with every change to a …

Continue Reading

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

Logical Functions

  • How to return blank in place of #DIV/0! error in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add business days to date in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • How to Calculate Age in Excel
  • Get fiscal year from date in Excel
  • Get month from date in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into unequal buckets in Excel
  • Group times into 3 hour buckets in Excel

General

  • 44 Practical Excel IF function Examples
  • How to calculate total from percentage in Excel
  • How to count total number of cells in a rectangular range in Excel
  • Check if range contains a value not in another range in Excel
  • Share Excel data with Word documents
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning