Showing posts with label Mr King Excel Tips. Show all posts

05 May, 2020

How to use the XOR Function in Excel

Bottom Line: Learn how to use the XOR function in Excel to analyze attendance data.

Skill Level: Intermediate

Download the Excel File

The file that I work within the video can be found below. You can use it to follow along and reconstruct what I'm doing in the video.

Dunder Mifflin Attendance Report - Follow Along.xlsx (24.4 KB)

Dunder Mifflin Attendance Report - Final.xlsx (166.6 KB)

Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.

I'm planning to post a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.

LET: Storing Variables Inside Your Excel Formulas

eama Mr King Excel Tips ,

LET: Storing Variables Inside Your Excel Formulas

Formulas in Excel are already a programming language. When you build a model in Excel, you are essentially writing a program to calculate a set of outputs from a set of inputs. The Calc team Redmond have been working on a couple of enhancements to the Excel formula language to make Excel a bit more like a programming language. The first of these, the LET function is now out in beta. Anyone who opts into the Insider Fast channel of Office 365 should have access to LET.

Sometimes you build a formula that has to refer to the same sub-calculation over and over again. The LET function lets you define a variable and the calculation for that variable. Your calculation can have up to 126 variables. Each variable can re-use the calculations in the previous variables. The final argument in the LET function is a formula that returns a value (or an array) to the cell. That final formula will refer to variables defined earlier in the LET function.

This is easiest to see if I show you an example. I randomly found a formula posted at the MrExcel Message Board in 2010. This formula, from member Special-K99 is designed to find the next-to-last word in a phrase.

If I were to build the original formula step-by-step, I would build it in steps.

Step 1: In B4, take the TRIM of the original phrase to get rid of repeated spaces.

TRIM function to get rid of repeated spaces.

Bill's "How Would You Clean This Data" Challenge

eama Mr King Excel Tips ,

Bill's "How Would You Clean This Data" Challenge

When I do a live Power Excel seminar, I offer that if anyone in the room ever has an odd Excel problem, they can send it to me for help. That is how I came to receive this data cleansing problem. Someone had a summary worksheet that looks like this:

They wanted to reformat the data to look like this:

One interesting clue about this data: The 18 in G4 appears to be a subtotal of H4:K4. It is tempting to remove columns G, L, and so on but first you have to extract the employee name from G3, L3, and so on.

It was 4AM on Sunday February 9th when I turned on the video recorder and recorded some clunky steps in Power Query to solve the problem. Given that it was Sunday, a day that I don't normally do videos, I asked for people to send in their ideas of how to solve the problem. 29 solutions have been sent in.

Each solution offers some cool new improvement over my process. My plan is to start a series of articles that shows the various improvements to my method.

Watch Video

Before I start that process, I invite you to see my solution:

And the M-code that Power Query generated for me:

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category Description", type text}, {"Dept. Total", type number}, {"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", Int64.Type}, {"Employee 1", type number}, {"Q1_1", type number}, {"Q2_2", type number}, {"Q3_3", Int64.Type}, {"Q4_4", Int64.Type}, {"Employee 2", Int64.Type}, {"Q1_5", Int64.Type}, {"Q2_6", Int64.Type}, {"Q3_7", Int64.Type}, {"Q4_8", Int64.Type}, {"Employee 3", Int64.Type}, {"Q1_9", Int64.Type}, {"Q2_10", Int64.Type}, {"Q3_11", Int64.Type}, {"Q4_12", Int64.Type}, {"Employee 4", type number}, {"Q1_13", type number}, {"Q2_14", type number}, {"Q3_15", type number}, {"Q4_16", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category Description"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "TextValue"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length([TextValue])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if [Length] > 2 then [TextValue] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Category Description", "Name", "TextValue", "Value", "Length"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([TextValue] = "Q1" or [TextValue] = "Q2" or [TextValue] = "Q3" or [TextValue] = "Q4")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Name] <> "Dept. Total"),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Length"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[TextValue]), "TextValue", "Value", List.Sum),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Name", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each [Q1]+[Q2]+[Q3]+[Q4])
in
    #"Added Custom1"

Before we start getting in to the solutions, let's address a lot of common comments:

Some of you said you would go backwards to figure out why the data is showing up in this format to begin with. I appreciate these comments. Everyone who said this is a better person than I am. I have learned over the years that when you ask "Why?" the answer usually involves this former employee who started down this path 17 years ago and everyone keeps using it this way since we are all used to it now.
Also – many of you – said the final solution should be a tall vertical table and then use a pivot table to produce the final results. Jonathan Cooper summarized this best: "I also agree with some of the other YouTube comments that a proper data set wouldn't have "Totals" and wouldn't have need to be pivoted at the end. But if the user really wants a plain old table then you give them what they want." I can actually see both sides of this. I love a pivot table and the only thing more fun than Power Query is Power Query with a nice pivot table on top. But if we can do the whole thing in Power Query, then one less thing to break.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

Excel 2019: Turn Data Sideways with a Formula

eama Mr King Excel Tips ,

Excel Turn Data Sideways with a Formula. Photo Credit: Pauline Loroy at Unsplash.com

Excel 2019: Turn Data Sideways with a Formula

Someone built this lookup table sideways, stretching across C1:N2. I realize that I could use HLOOKUP instead of VLOOKUP, but I prefer to turn the data back to a vertical orientation.

Copy C1:N2. Right-click in A4 and choose the Transpose option under the Paste Options. Transpose is the fancy Excel word for “turn the data sideways.”

Copy the horizontal lookup table in C1:N2. Right-click in a blank cell. The fourth icon under Paste Options is called Transpose. Choose that and you will paste a sideways copy of the original table.

I transpose a lot. But I use Alt+E, S, E, Enter to transpose instead of the right-click.

There is a problem, though. Transpose is a one-time snapshot of the data. What if you have formulas in the horizontal data? Is there a way to transpose with a formula?

The first way is a bit bizarre. If you are trying to transpose 12 horizontal cells, you need to select 12 vertical cells in a single selection. Start typing a formula such as =TRANSPOSE(C2:N2) in the active cell but do not press Enter. Instead, hold down Ctrl+Shift and then press Enter. This puts a single array formula in the selected cells. This TRANSPOSE formula is going to return 12 answers, and they will appear in the 12 selected cells, as shown below.

Select the 12 cells B4:B15. Type a formula =TRANSPOSE(C2:N2) but do not press Enter. Instead, hold while holding down Ctrl and Shift, press Enter. This enters a single formula in 12 cells at one time.

As the data in the horizontal table changes, the same values appear in your vertical table, as shown below.

The results: as the original formulas in C2:N2 recalculate, the results in the vertical range are updated.

But array formulas are not well known. Some spreadsheet rookie might try to edit your formula and forget to press Ctrl+Shift+Enter.

To avoid using the array formula, use a combination of INDEX and ROW, as shown in the figure below. =ROW(1:1) is a clever way of writing the number 1. As you copy this formula down, the row reference changes to 2:2 and returns a 2.

The INDEX function says you are getting the answers from C2:N2, and you want the nth item from the range.

A different solution that does not require Ctrl+Shift+Enter. The formula for January in B4 is =INDEX($C$2:$N$2,ROW(1:1)). The ROW(1:1) is a complicated way to write the number 1.

In the figure below, =FORMULATEXT in column C shows how the formula changes when you copy down.

As you copy that formula down, the reference to ROW(1:1) automatically changes to ROW(2:2) and so on.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Geography & Stock Data Types in Excel

eama Mr King Excel Tips ,

Geography & Stock Data Types in Excel

In the past, Excel did not really handle data types. Yes, you could format some cells as Date or Text, but the new data types provide a whole new entry point for new data types now and in the future.

Start with a column of City names. For large cities like Madison Wisconsin, you can just put Madison. For smaller towns, you might enter Madison, FL.

A list of cities in A2:A8. Houston, El Paso, and Madison stand on their own. But smaller cities need the state: Paris, KY.

From the Data tab, select Geography.

The new Data Types gallery on the Data tab offers Stocks and Geography. Choose Geography.

Excel searches the Internet and finds a city for each cell. A folded map appears next to each cell. Notice that you lose the state that was in the original cell.

Each value in A2:A8 now has a map icon to the left of the text. One annoyance, Paris, KY now just says Paris.

Click on the Map icon and a data card appears with information about the city.

Click any map icon and a box pops up with information such as Population, County, Mayor.

The best part: for any data in the card, you can use a formula to pull that data into a cell. Enter =A2. Population in cell B2 and Excel returns the population of El Paso. Double click the Fill Handle in B2 and Excel returns the population for each city.

If El Paso is in A2, type =A2.Population in B2 to get the population of 683,080. Copy the formula down and you see the population for each city.

Caution

These new formulas might return a #FIELD! error. This means, Excel, or more correctly Bing, does not know the answer to this yet, but it may do so at some time in the future. It is not an error with the formula or the table, just a lack of knowledge currently.

The Geography and Stock data types have extra features if you format as a table using Ctrl+T.

A new Add Data icon appears to the right of the heading. Use this drop-down menu to add fields without having to type the formulas. Clicking the icon will enter the formula for you.

The Add Data icon appears near the top of the table, just outside the right edge.

You can also sort the data by any field, even if it is not in the Excel grid. Open the drop-down menu for the City column. Use the new Display Field drop-down to choose Longitude.

Another trick: open the filter drop-down for City and you can choose to sort City by Longitude, even though Longitude is not in the table.

With Longitude selected, choose sort Smallest to Largest.

After choosing Longitude in the City drop-down, choose Sort Smallest to Largest. This will sort cities West-to-East.

The result: data is sorted west to east.

Cities are sorted west to east. Column B has the population. Column C has the state. Column D shows the county.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Use Data, Refresh All to Update Stock Data

eama Mr King Excel Tips ,

Use Data, Refresh All to Update Stock Data

The other data type available is Stock data. Enter some publicly held companies:

Four cells have Microsoft, Netflix, Southwest Airlines, and Coca Cola.

Choose, Data, Stocks. An icon of a building with Roman columns should appear next to each company You can add fields such as CEO, Price, Volume, High, Low, Previous Close.

Use the Stocks data type icon and convert the company names to data types. Data type formulas let you ad CEO, stock price.

In contrast to Geography, where population might only be updated once a year, the stock price will be constantly changing throughout the trading day. Rather than go out to the Internet with every recalc, Excel will only updated the data from these Linked Data Types when you choose Refresh.

One easy way to update the stock prices is to use the Refresh All icon on the Data tab.

To force the stock price to update, use the Refresh All icon on the Data tab.

As the name implies, Refresh All will update everything in your workbook, including any Power Query data connections which might take a long time to update. If you want to only refresh the current block of linked data, right-click on A2, choose Data Types, Refresh.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Excel 2019: Dynamic Arrays Can Spill

eama Mr King Excel Tips ,

Excel 2019: Dynamic Arrays Can Spill

Introduced in 2019, the new Dynamic Arrays represent a major change to the Excel calculation engine. While the old Ctrl+Shift+Enter array formulas could return several results into a pre-selected range, these new formulas do not require Ctrl+Shift+Enter and you don't have to pre-select the range.

For example, let's say you type =B2:B8*C1 into cell C2. In the past, this formula would have required dollar signs around $C$1. You would copy the formula to all 7 cells and something called implicit intersection would make sure the numbers were correct.

Dollar amounts in B2:B8. A 10% factor in C1. In the screen shot, something completely new: a single formula of =B2:B8*C1 being entered in one cell, C2.

But now, with Dynamic Arrays, one formula in cell C2 will spill over and return results into many cells.

Press Enter and the one formula returns all 7 answers. No one had to remember Ctrl+Shift+Enter.

When you select any cell from C2:C8, a blue outline appears around the cells to let you know that the values are the result of a single formula. That single formula only exists in C2. If you select any other cell in the range, the formula appears in the formula bar, but it is greyed out.

If the range B2:B8 grows (by someone inserting rows in the middle), the spilled results will grow as well. However, simply typing new values in A9:B9 will not cause the formula to extend, unless you format the whole range with Ctrl+T before adding values.

Although the results are showing in C2:C8, the formula only lives in C2. When you select C3, the formula in the formula bar is greyed out.

What if a formula can not spill? What if there are non-empty cells in the way? Rather than return partial results, the formula will return the new #SPILL! error.

If you try to enter that formula in C2 and there is already stuff in the way in C4, you wll get a #SPILL! error in C2.

Open the yellow dropdown to the left of the error and you can select the obstructing cells.

Open the drop-down to the left of the #SPILL! error and the message says Spill Range Isn't Blank. One option in the menu is Select Obstructing Cells.

Once you clear the obstructing cells, the answers will appear again.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

The FILTER function in Excel

eama Mr King Excel Tips ,

The FILTER function is new as part of the dynamic arrays feature. There are three arguments: array, include, and an optional [if empty].

The Filter function has three arguments: Array, Include, and an optional [if_empty])

Say you want to be able to enter a team name in G1 and extract all of the records for that team. Use a formula of =FILTER(B3:E9,C3:C9=G1).

With Red entered in G1, you get all of the records for the red team returned to G3:J6.

If cell G1 changes from Red to Blue, the results change to show you the blue team records.

Change G1 from Red to Blue and you get three records returned, representing the rows for the Blue team.

In the above examples, the optional [If Empty] argument is missing. If someone is allowed to enter the wrong team name in G1, then you will get a #CALC! error.

The #CALC! error appears when you type Yellow in G1. Since there are no matching records, the FILTER function is returning an empty array. As of February 2019, empty arrays are not supported in Excel, so you get the #CALC! error. The official definition of #CALC! is "we can't calculate this today, but we might be able to calculate it in the future."

To avoid the #CALC! error, add a third argument.

Use the optional if_empty argument with "None Found" and the #CALC! error changes to "None Found".

You can specify an array constant for the third argument if you want to fill each column of the answer array.

But here is a problem: the FILTER function should have been returning four values. The "None Found" entered in the previous screenshot only fills one column. You can use an array constant of {"No","Team","O",0} to fill all four columns when nothing matches.

To filter to records where multiple conditions are met, multiply the conditions together.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Handle Plural Conditions with SUMIFS

eama Mr King Excel Tips ,

Handle Plural Conditions with SUMIFS

Did you notice the “S” that got added to the end of SUMIF starting in Excel 2007? While SUMIF and SUMIFS sound the same, the new SUMIFS can run circles around its elder sibling.

The old SUMIF and COUNTIF have been around since Excel 97. In the figure below, the formula tells Excel to look through the names in B2:B22. If a name is equal to the name in F4, then sum the corresponding cell from the range starting in D2:D22. (While the third argument could be the first cell of the sum range D2, it will make the function volatile, causing the worksheet to calculate more slowly.)

A summary table built with SUMIF. Go find all of the sales made by this person.

SUMIF and COUNTIF were great with only one condition. But with two or more things to check, you had to switch over to SUMPRODUCT, as shown below. (I realize most people would replace my multiplication signs with commas and add a double-minus before the first two terms, but my version works, too.)

Before SUMIFS was introduced, you would have to use SUMPRODUCT when you needed to check for two conditions.

SUMIFS allows for up to 127 conditions. Because you might have an indeterminate number of conditions in the function, the numbers that you are adding up move from the third argument to the first argument. In the following formula, you are summing D2:D22, but only the rows where column B is Allen Matz and column C is Widget. The logic of "Sum this if these conditions are true" is logical in SUMIFS.

The formula =SUMIFFS($D$2:$D$22,$B$2:$B$22,$F4,$C$2:$C$22,G$3) says to sum the quantities in D when the sales rep in B matches F4 and when the Product in C matches G3.

Excel 2007 also added plural versions of COUNTIFS and AVERAGEIFS. All these “S” functions are very efficient and fast.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

One Hit Wonders with UNIQUE

eama Mr King Excel Tips ,

One Hit Wonders with UNIQUE

For me, I can't imagine why I would ever need a list of items that have been sold exactly once. My only example is the One-Hit Wonders segment on Casey Kasem's American Top 40 radio show.

To get a list of artists who had exactly one hit, use =UNIQUE(B4:B6132..True). In the figure below, the UNIQUE function is wrapped in a SORT function so the resulting list is alphabetical.

By using TRUE for the Occurs_Once argument in UNIQUE, you can find the artists who only appeared once in the original database. Sort those alphabetically using =SORT(UNIQUE(B4:B6132,False,True)).

To get the titles in column J, a VLOOKUP uses an array as the first argument. This is pretty wild - one VLOOKUP formula is actually doing over 1000 lookups and returning all 1000 results.

To get the title next to the artist, use =VLOOKUP(I5#,B4:C6132,2,False). You know that these one-hit-wonder artists only appear once in the database, so VLOOKUP works.

Another approach is to use a FILTER function combined with IFERROR and MATCH.

Use =FILTER(A4:D6132,IFERROR(MATCH(B4:B6132,UNIQUE(B4:B6132,False,True),0),False)) to return all four columns from the table.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

SEQUENCE inside of other Functions such as IPMT

eama Mr King Excel Tips ,

SEQUENCE inside of other Functions such as IPMT

After SORT, SORTBY, FILTER, and UNIQUE, the SEQUENCE and RANDARRAY functions seem pretty tame. SEQUENCE will generate a sequence of numbers.

The syntax for SEQUENCE is Rows, Columns, Start, Step.

It does not seem like this is very interesting. Who needs to generate a list of numbers?

=SEQUENCE(5) returns the numbers 1 through 5 in a column. =SEQUENCE(5,2,3,9) returns 5 rows and 2 columns, starting with 3, incrementing by 9. The results of this second formula are 3 and 12 in the first row, 21 and 30 in the second row, and so on.

Try putting SEQUENCE inside other functions. Here, IPMT calculates the interest in the 7th month of a loan:

To calculate the interest during the 7th month of a loan, use =IPMT(H5/12,7,H4,H3). The next example will replace that 7 with a SEQUENCE function.

Thanks to SEQUENCE, this formula calculates the interest paid during 12 months starting in month 7:

To calculate the interest for months 7 through 18, use =SUM(IPMT(H5/12,SEQUENCE(12,1,7),H4,H3).

Two formulas create a forward-looking calendar:

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Replace a Pivot Table with 3 Dynamic Arrays

eama Mr King Excel Tips ,

Replace a Pivot Table with 3 Dynamic Arrays

As the co-author of Pivot Table Data Crunching, I love a good pivot table. But Excel Project Manager Joe McDaid and Excel MVP Roger Govier both pointed out that the three formulas shown here simulate a pivot table and do not have to be refreshed.

To build the report, =SORT(UNIQUE(C2:C392)) provides a vertical list of customers starting in F6. Then, =TRANSPOSE(SORT(UNIQUE(A2:A392))) provides a horizontal list of products starting in G5.

When you specify F6# and G5# in arguments of SUMIFS, Excel returns a two-dimensional result: =SUMIFS(D2:D392,C2:C392,F6#,A2:A392,G5#).

Four columns of source data: Product, Date, Customer and Revenue. Headings are in row 1, data is rows 2:392. =SORT(UNIQUE(C2:C392)) gets a vertical sorted list of customers starting in F7. Then, =TRANSPOSE(SORT(UNIQUE(A2:A392))) gets a horizontal list of products in G5. Finally, =SUMIFS(D2:D392,C2:C392,F6#,A2:A392,G5#) in G6 fills in the Revenue amount for each combination of Customer and Product.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Replace Ctrl+Shift+Enter with Dynamic Arrays

eama Mr King Excel Tips ,

Replace Ctrl+Shift+Enter with Dynamic Arrays

Before dynamic arrays, people would use these crazy Ctrl+Shift+Enter formulas.

Say that you have a friend who is superstitious about Friday the 13th. You want to illustrate how many Friday the 13ths your friend has lived through. Before Dynamic Arrays, you would have to use the formula below.

Before dynamic arrays, the formula to calculate the number of Friday the 13ths between B1 and B2 is =SUMPRODUCT( --(DAY(ROW(INDIRECT(B1&":"&B2)))=13), --(WEEKDAY(ROW(INDIRECT(B1&":"&B2)),2)=5)) entered with Ctrl+Shift+Enter.

The same formula after dynamic arrays is still complicated, but less intimidating:

With dynamic arrays, the formula is =SUMPRODUCT( (DAY(SEQUENCE(B4,,B3))=13)* (WEEKDAY(SEQUENCE(B4,,B3),2)=5)) entered without Ctrl+Shift+Enter.

Another example from Mike Girvin's Ctrl+Shift+Enter book is to get a unique list.

A helper formula in D2 counts the number of unique items in a list. Then, a formula returns the unique list in D5. See the next screenshot for the formula.

Here is the formula. I won’t try to explain it to you.

This was the insane formula to get the unique list before Dynamic Arrays: =IF(ROWS(D$5:D5)>$D$2,"", INDEX($B$2:$B$146, SMALL(IF(FREQUENCY(IF($B$2:$B$146<>"", MATCH($B$2:$B$146,$B$2:$B$146,0)), ROW($B$2:$B$146)-1), ROW($B$2:$B$146)-1), ROWS(D$5:D5))))

The replacement formula with dynamic arrays is =UNIQUE(B2:B146).

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

05 May, 2020

How to use the XOR Function in Excel

Download the Excel File

04 May, 2020

LET: Storing Variables Inside Your Excel Formulas

14 March, 2020

Bill's "How Would You Clean This Data" Challenge

Watch Video

27 January, 2020

Excel 2019: Turn Data Sideways with a Formula

25 January, 2020

Geography & Stock Data Types in Excel

24 January, 2020

Use Data, Refresh All to Update Stock Data

Excel 2019: Dynamic Arrays Can Spill

20 January, 2020

Handle Plural Conditions with SUMIFS

19 January, 2020

One Hit Wonders with UNIQUE

SEQUENCE inside of other Functions such as IPMT

09 January, 2020

Replace a Pivot Table with 3 Dynamic Arrays

Replace Ctrl+Shift+Enter with Dynamic Arrays

Popular Posts

Best ebooks today

Labels

Facebook

Total Pageviews

Search This Blog

Translate

↓ Main

Popular Posts

Recent

Now

Featured

Public

Blog Archive

Popular Posts

Recent Post

Subscribe

Tags

About me

Recent Comments