Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • CTRL+Y a mini macro Excel User tips
  • Sheet() and Sheets() Lookup and References
  • Expenses calendar, FaCal Basic Math
  • Fx to cut long column into two columns Formulas - combined functions
  • DayName Date and Time
  • Sort list dynamically (functions) COLUMN
  • Filter. Google Sheets function Google Sheets
  • Shapes with dynamic output Excel User tips

Category: Standard functions

Standard functions that comes with Excel, plain single functions

Multiple-Validations (connected to each other)

Posted on February 7, 2012 By ANmar No Comments on Multiple-Validations (connected to each other)
Multiple-Validations (connected to each other)

Here is the free file for a special request by a client
He wanted to have multiple cells having Data – Validation to same list but minus the selected one

In English, once you select one from cell1, cell2 will bring the same list but without the selected one, then when you select another item is selected in cell2, cell3 will bring what left from the list.

Does that make sense, check out the screenshots

Read More “Multiple-Validations (connected to each other)” »

Data Validation, Formulas - combined functions, IF, Logical, Standard functions

WorksheetName and WorksheetsNo UDFs

Posted on February 7, 2012 By ANmar No Comments on WorksheetName and WorksheetsNo UDFs
WorksheetName and WorksheetsNo UDFs

Once you open the sheet and enable macros, you can use function:

=WorksheetsNo()

To get total number of sheets in that workbook

And use function:

=WorksheetName(1)

To get sheet name for first sheet, and

Read More “WorksheetName and WorksheetsNo UDFs” »

Lookup and References, ROW, User-Defined f(x)s = UDF, Worksheet

Dynamic selection list

Posted on February 7, 2012 By ANmar No Comments on Dynamic selection list
Dynamic selection list

Using Index and CountA Formulas, Names and Forms > ListBox to have a list with connected formulas

Can also be used as form to read from a large table in another sheet

 

After creating two names, one is “Months” having:

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1)

And the other one is “Month_All” having:

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3)

Draw a listbox using “Developer toolbar”, then make its input list as “Months” name we did above, then use these formulas:

Read More “Dynamic selection list” »

ActiveX controls, COUNTA, Formulas - combined functions, INDEX, Lookup and References, Names, Standard functions

Validation based on another Validation

Posted on February 7, 2012 By ANmar No Comments on Validation based on another Validation
Validation based on another Validation

Creating a Data Validation based on another Data Validation

Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it.

You need to create two names (Formula > Name Manager), one has this formula

=OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2))

The other one has this

Read More “Validation based on another Validation” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, MATCH, OFFSET, Standard functions

Rotate table using 1 formula

Posted on February 6, 2012 By ANmar No Comments on Rotate table using 1 formula
Rotate table using 1 formula

Rotating table (Transpose) using functions so that the table is updated once the source table is updated. Also can be used as automatically rotating tables with variable number of rows and columns Sheet name can be used inside formula so that you can have multiple tables from different sheets to be rotate it into one…

Read More “Rotate table using 1 formula” »

ADDRESS, COLUMN, Formulas - combined functions, INDIRECT, Lookup and References, ROW, Standard functions

Sort list dynamically (functions)

Posted on February 5, 2012 By ANmar No Comments on Sort list dynamically (functions)
Sort list dynamically (functions)

Sorting a list automatically using formulas, with no need to press the sort command Also if the source table is changed, the destination table will do also. What you need is basically two formulas, one for the sort-by column to list items by order. Use SMALL to sort ascending, or LARGE to sort descending, then…

Read More “Sort list dynamically (functions)” »

COLUMN, Formulas - combined functions, INDEX, LARGE, Lookup and References, MATCH, Math and Trig, ROW, SMALL, Standard functions

Insert Blank rows into table using functions

Posted on February 5, 2012 By ANmar No Comments on Insert Blank rows into table using functions
Insert Blank rows into table using functions

Here you will see how to work with functions to insert a blank row every certain number of rows in a table using a blank sheet to copy all values of that table into the new sheet Then using Copy > Paste Special to make them constants The new thing is that this is all…

Read More “Insert Blank rows into table using functions” »

CHAR, COLUMN, Formulas - combined functions, IF, INDIRECT, INT, Lookup and References, ROW, Standard functions

Change Calculator – calculate change in multiple bills/coins

Posted on February 1, 2012 By ANmar No Comments on Change Calculator – calculate change in multiple bills/coins
Change Calculator – calculate change in multiple bills/coins

An Excel file that will determine what cash to return to the buyer (How much of each cash unit, or how many of Fifty dollars and how many of Twenty dollars and so on) in a very interesting way using as less functions as needed So, if you need to return 15.45, this file will…

Read More “Change Calculator – calculate change in multiple bills/coins” »

Formulas - combined functions, IF, INT, Logical, Math and Trig, Standard functions, SUMPRODUCT

Progress bar in pure functions

Posted on January 29, 2012 By ANmar No Comments on Progress bar in pure functions
Progress bar in pure functions

This one is very similar to Tally chart, however, it is for another purpose. It is used to draw a progress bar, yes a progress bar in pure functions, using the formula: =REPT( CHAR( 8),INT( D2/10))&REPT( CHAR( 7),INT( D3/10)-INT( D2/10))

CHAR, Formulas - combined functions, INT, REPT, Standard functions, Texts and Strings

Excel Dynamic Drop-Downs that grow with you

Posted on January 29, 2012 By ANmar
Excel Dynamic Drop-Downs that grow with you

This will help you do a dynamic name to use on a drop down, a Form Listbox or Form Combobox. The screenshot almost says it all. You need 4 steps: 1- Create the name for a column in your Excel file, go to Names > Define in Excel2003 or earlier, or go to Formulas >…

Read More “Excel Dynamic Drop-Downs that grow with you” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, OFFSET, Standard functions

Tally chart in functions

Posted on January 26, 2012 By ANmar No Comments on Tally chart in functions
Tally chart in functions

Using a single function can make a graphical view to your number Function is REPT along with IF and INT to show that number in graphical view, formula used here is: =IF(B4<5,REPT(“|”,B4),REPT(REPT(“|”,5)&”-“,INT(B4/5))&REPT(“|”,B4-INT(B4/5)*5))

Formulas - combined functions, IF, INT, Logical, Math and Trig, REPT, Standard functions, Texts and Strings

Posts pagination

Previous 1 … 6 7 8 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
  • Scorring, saving multiple outputs in 1 number Basic Math
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions
  • Text-to-Columns, dynamically using formulas IFERROR
  • Walking Columns ActiveX controls
  • OFFSET+MATCH Formulas - combined functions
  • Get 3rd Wednesday of month Basic Math
  • Formula Beautifier Excel User tips
  • Count cells with condition – multiple conditions COUNTIFS

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme