Posts

For more details on EDATE, see:How to use the EDATE function. SEQUENCE function The SEQUENCE function is designed to generate numeric sequences in rows and/or columns. For more details on SEQUENCE, seeHow to use the SEQUENCE function. The format “mmmm” tells the TEXT function to extract the full month name from a given date. Older versions of Excel In older versions of Excel, there is no SEQUENCE function. This means we don’t have an easy way to calculate and return 12 dates all at once....

The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel times are just numbers, SEQUENCE works well for generating times. In the first worksheet, let’s generate 9 times, one hour apart, starting at 9:00 AM. ForstartI’ll use 9:00 AM. Fortimes, I use 9. And forstepI use 1 hour. In cell E5, I’ll set up the SEQUENCE function to use these values. Rowsis the Times value in C5....

The start date is entered in cell B5. The number of dates to create (n) is entered in cell B8. If either of these two values are changed, a new list of weekend dates should be generated. WORKDAY.INTL will automatically exclude weekends and can optionally exclude dates that are holidays. In this code, a 1 indicates a weekend and a 0 indicates a workday. Each digit in the code represents a day of the week....

Explanation The goal is to generate a dynamic list of sequential working days with a formula. Weekends and holidays are omitted from the result. Note that the number of dates to return is hardcoded into the formula as 12. However, this value could easily be exposed on the worksheet as another variable. SEQUENCE function Working from the inside out, let’s look first at theSEQUENCE function. SEQUENCE is designed to generate numeric sequences in rows and/or columns....

Explanation The goal is to generate a series of dates one year apart. Both methods are described below. SEQUENCE function TheSEQUENCE functiongenerates numeric arrays. Themonthis provided as 5 anddayis provided as 1. Then it adds 1 to the year value and returns the results to theDATE functionwhich creates a new date. The reason we do this is to maintain the workbook structure, with the start date in cell B5. It’s a good example of how thedynamic arraycreated by SEQUENCE provides a more compact, elegant solution....

Odd row groups return FALSE so no conditional formatting is applied. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula. CEILING Function The Excel CEILING function rounds a given numberupto the nearest specified multiple. CEILING works like theMROUND function, but CEILINGalways rounds up. ISEVEN will return the #VALUE error if a value is not numeric....

This can be a nice way to make certain tables easier to read. Let’s use conditional formatting to highlight these rows to match the data. We can do this with a formula that groups rows by three’s. As usual, I’ll set up dummy formulas to figure out a working formula. Then I’ll create a conditional formatting rule in the final step. To start off, I want to get a row number for each row, starting with 1....

The SHEET function includes hidden sheets in the numbering sequence. Whenvalueis omitted, SHEET will return a numeric index for the current sheet (i.e. the sheet the formula exists in). When no arguments are supplied SHEETS returns the total number of sheets in the workbook.

SHEETS takes one argument,reference, which should be a cell reference, or a3D reference. When no references are supplied SHEETS returns the total number of sheets in the workbook. The SHEETS function includes hidden sheets. SHEET will report the sheet number for a cell reference, named range, or Excel Table.

The trick is to use the control key while you press enter. Let’s take a look. Or, you could use thefill handle. But there’s a faster way to do it. Just select all the cells from the start and key in the data you want to enter. Then, while holding down the control key, press the enter key. The value is then entered into all cells at once. This same trick works even when cells are not next to one another....

In this first example, we have a big set of data that contains a lot of blank rows. To quickly remove these blank rows, I can use Go To Special. First, pick a column that should always contain data. In this case, Column A should always have a name. Then go for the column, and use Ctrl + G for Go To. Then Special, then select Blanks. At this point, every blank cell in column A is selected....

I have a spreadsheet that contains music data. To fix this, I need to add the correct values for each blank cell. On the surface, this looks like a really tedious job. But, there’s actually a really easy way to add the missing data using a simple shortcut recipe. For this, use Control Enter. Now all blank cells have a value. This works because it creates a chain of formulas that all get a value from the cell above....

This worksheet contains measurement data. Here’s a nice workaround, when you have this problem. It’s actually useful shortcut recipe itself. This gets you near the bottom of the data. Now, move right and extend the selection up. Ok, so now I have a count of all non-blank cells. Now, I could filter the list for zero values, and then delete. But I can also use Go To Special. For that, I’ll just use the IF function....