Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Rotate table using 1 formula ADDRESS
  • Search across sheets in function ADDRESS
  • Workdays – Across months DATE
  • Limited Ceiling Basic Math
  • Cell formats 0.0\% and 0.0%;(0.0%) Format Cells
  • Links between two workbooks – dynamically using functions INDIRECT
  • Standard math to calculate Aspect Ratio Basic Math
  • Count Unique COUNTIF

Category: Data Validation

Custom fiscal year calendar (CFlex)

Posted on December 5, 2018 By ANmar

Fully customized Yearly Calendar to show certain dates with certain format using conditional formatting, like mark 8th business day each month with certain color, 1st workday after 15th of each month, etc.
See the sheet “Data” for details and table of specific dates and rules. Contact me for more detail

Read More “Custom fiscal year calendar (CFlex)” »

Basic Math, Conditional Formatting, Data Validation, DATE, Date and Time, MONTH, Non-functions, Standard functions, TODAY, WEEKDAY, 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

State Abbr with Index+Match+Validation

Posted on February 14, 2012 By ANmar No Comments on State Abbr with Index+Match+Validation
State Abbr with Index+Match+Validation

Small file that will show how to do INDEX + MATCH formulas to get the abbreviation of the state based on its name or the name of the state based on its abbreviation

Main formula to do that is:

=INDEX(Abbr_States,MATCH(B4,States,0),1)

Read More “State Abbr with Index+Match+Validation” »

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

Multiple Dual-Validations

Posted on February 9, 2012 By ANmar No Comments on Multiple Dual-Validations
Multiple Dual-Validations

It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it

Mutliple-dual-validations

that depend on each other.

Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the main that used in column B in that row.

Creating something like this basically falls in three parts

Part1: is the Names you need to define, so after we create the table found “Data” sheet …..

We need to set two names, one for the left-top cell for that table (used as reference) and another name with formula:

Read More “Multiple Dual-Validations” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, MATCH, Names, OFFSET, Standard 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

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

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

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
  • Comments versus Notes Excel User tips
  • Links between two workbooks – dynamically using functions INDIRECT
  • Sheet() and Sheets() Lookup and References
  • CountIf limitation COUNTIF
  • Multiple lines in cell using functions CHAR
  • Expenses calendar, FaCal Basic Math
  • Unix DateTime Number Basic Math
  • SimpleANBox Basic Math

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme