Excel

All things Microsoft Excel

Excel 365 Sequence Function

Everyone knows the trick to creating a sequence of number in Microsoft Excel.  Start the sequence and then select it and drag down the column (or rows) creating the sequence of numbers.

image.png            image.png

However, in the Office 365 version of Microsoft Excel there is a new dynamic array formula

=SEQUENCE(rows, [columns], [start], [step])

Positive sequence 1 to 20 example

image.png

Negative sequence 20 to 1 example

image.png

Positive sequence 1 to 6 across columns

image.png

Sequence of 20 days ascending

image.png

Sequence of the months of the year

image.png

Sequence of numbers across 10 rows and 5 columns starting at 0 and stepping by 3

image.png

Last one - Sequence of numbers 1 to 20 with a prefix AND the number only shows if there is an item in the B column

="No. "&SEQUENCE(COUNTA(B:B))

image.png            mHLimage.png






Excel Autofill Letters from A to Z

This formula tip will let you fill a column with the letters A to Z.

Start by putting the letter A in a cell.  I put the letter A in the cell A1 for the example below.  Then in cell A2 I inserted the formula.  Note I reference the cell A1.  Then I simply copied the formula down to the A26 cel and viola I have the alphabet automagically.

=CHAR(CODE(A1)+1)

image.png

Excel Compact U.S. Calendar Template

MyCalCreatorTemplate.xltx

I want to give credit to David Seah for the original idea for this compact calendar.  I took David's calendar idea and modified a number of formulas especially the way the holidays are calculated as well as the addition of other features.