Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Sorting with functions only CHOOSE
  • Why I ran from =INDIRECT() function Formulas - combined functions
  • Edit directly in cell Excel User tips
  • When Excel tells you to go get some life Excel User tips
  • Unix DateTime Number Basic Math
  • Excel Functions = XL f(x)s XLfxs
  • FORMULATEXT to show formulas FORMULATEXT
  • Week number to Sunday date – Weeknum reverse DATE

Category: OFFSET

Walking Columns

Posted on January 14, 2023 By ANmar
Walking Columns

An easy way to scroll through set of data in a graphic way without graphs.

A-pure-formula xlsx workbook. These were done using formulas, conditional formatting, and ActiveX control.

Read More “Walking Columns” »

ActiveX controls, Basic Math, COLUMN, Conditional Formatting, Formulas - combined functions, IF, INT, Logical, Lookup and References, Math and Trig, Non-functions, OFFSET, ROW, Standard functions

Repeated Offset

Posted on September 19, 2022 By ANmar
Repeated Offset

I needed to have an offset of columns to be repeated every 12 months.

This is part of a project, we needed to calculate the coefficient of sales prediction based on the month number, we do have coefficient saved in another table that repeated every year (12 months).

Read More “Repeated Offset” »

Basic Math, COLUMN, Formulas - combined functions, IFERROR, INT, Logical, Lookup and References, Math and Trig, OFFSET, Standard functions

Search across sheets in function

Posted on July 22, 2022 By ANmar
Search across sheets in function

Just when you need to search for a thing across sheets in a workbook.

I am bored, so I am adding articles from my old archive.

Read More “Search across sheets in function” »

ADDRESS, COUNTIF, Formulas - combined functions, INDIRECT, Lookup and References, OFFSET, Standard functions, Statistical

Min Max vehicle

Posted on July 20, 2022 By ANmar
Min Max vehicle

Finding which vehicle with minimum (or maximum) amount of savings among peers.

This is an old request, found its formula in my archive to calculate which of the 4 columns is actually has the minimum (or maximum) of savings among other columns.

Trick is as you guessed it was OFFSET.

Read More “Min Max vehicle” »

Formulas - combined functions, Lookup and References, MATCH, Math and Trig, MAX, Min, OFFSET, Standard functions

Looplist – Repeating months using formulas

Posted on December 11, 2018 By ANmar
Looplist – Repeating months using formulas

Using formulas to repeat list, I call it LoopList

Used to list Months for several years, in away that with 1 formula, it will loop through all months, then jumps into next year and loop through its months again, and so on

Read More “Looplist – Repeating months using formulas” »

Basic Math, Date and Time, Formulas - combined functions, MATCH, MONTH, OFFSET, ROW, YEAR

Skills Grid

Posted on October 26, 2018 By ANmar
Skills Grid

A small Excel file to show how we can create a chart-like Excel sheet

Used in my Resume to show different skill sets and the level of expertise in each

Read More “Skills Grid” »

Conditional Formatting, Data Validation, Formulas - combined functions, IF, IFERROR, Logical, Lookup and References, Names, Non-functions, OFFSET, Standard functions, VLOOKUP, Worksheet

OFFSET+MATCH

Posted on June 9, 2017 By ANmar
OFFSET+MATCH

One of the power of combining functions in one cell is using the best of these functions

An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.

Why do we want to do that?

VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time

We needed several times to locate a row by using a column on the right, then get the value from a column left to it.

This is when VLOOKUP can not help us.

Read More “OFFSET+MATCH” »

Formulas - combined functions, Lookup and References, MATCH, OFFSET, Standard functions

Why I ran from =INDIRECT() function

Posted on April 6, 2015 By ANmar No Comments on Why I ran from =INDIRECT() function
Why I ran from =INDIRECT() function

I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()

Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed

Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.

You see INDIRECT does a simple job

Converts a text that represent a reference, into reference

So…

If you do

=INDIRECT("G4")

It gives you the value in G4

Notice that “G4” has the double-quotations, means

=INDIRECT("G"&SUM(S1:S4))

Read More “Why I ran from =INDIRECT() function” »

Formulas - combined functions, INDIRECT, Lookup and References, OFFSET, Standard functions

Fx to cut long column into two columns

Posted on March 19, 2012 By ANmar No Comments on Fx to cut long column into two columns
Fx to cut long column into two columns

Convert one long column into two
This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column

This was the answer for this question
Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB

Show two columns in one page as four columns using functions only. This will help you to show the next x number of rows for a certain column to the next of the first one to reduce the number of pages to print.

Read More “Fx to cut long column into two columns” »

Formulas - combined functions, INT, Lookup and References, OFFSET, ROW, Standard functions

iframe in Excel (XLiFrame)

Posted on February 19, 2012 By ANmar No Comments on iframe in Excel (XLiFrame)
iframe in Excel (XLiFrame)

This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.

Formulas used to bring actual data is:

=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))

While formula used to bring header labels:

Read More “iframe in Excel (XLiFrame)” »

ADDRESS, COLUMN, Formulas - combined functions, IF, INDIRECT, LEFT, Lookup and References, OFFSET, Standard functions, Texts and Strings

Extract Address into table

Posted on February 10, 2012 By ANmar No Comments on Extract Address into table
Extract Address into table

Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table

Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above

Which is usually what you got from any list of addresses online, and you want to convert it to table.

This is exactly what one of clients had and needed, so here is the file that does that.

Basically you need to have 5 columns with formula in column G as:

=OFFSET($B$1,(ROW()-5)*4+4,0)

to extract first line (Starting from cell B5

Then in column H as:

Read More “Extract Address into table” »

Formulas - combined functions, LEFT, Lookup and References, MID, OFFSET, ROW, SEARCH, Standard functions, Texts and Strings

Posts pagination

1 2 Next

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Extract current (Active) workbook name CELL
  • Get monthly total from table with dates DATE
  • Middle name + 1st name Basic Math
  • VLookup Lookup and References
  • Dynamic selection list ActiveX controls
  • SimpleANBox Basic Math
  • iframe in Excel (XLiFrame) ADDRESS
  • Progress bar in pure functions CHAR

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme