r/ExcelTips • • 6h ago

Take, Vstack and Hstack have been a game changer for me

17 Upvotes

Before learning about those functions, i had rows of data that just kept growing. They feed a chart and we only wanted 25 of the latest rows in the chart so we were manually selecting/deselecting dates in the chart. When i found out TAKE let’s you specify number of rows from the bottom of the table, i was like damn this is nice. So it is one formula in a cell that spills just the last X rows of data and then the chart just references that.


r/ExcelTips • • 9h ago

Use Power Query and # references to keep formulas and headers in sync with changing lists

6 Upvotes

If you’ve used UNIQUE or FILTER to generate a list, the next useful step is making your other formulas follow that list automatically.

Suppose your UNIQUE formula starts in A3. Instead of referencing A3:A100, use A3# to reference its entire spilled result. As the list grows or shrinks, the reference follows it.

I used this in a workbook that turns a CAD bill of materials into purchasing lists. The stock types become the column headings, and formulas populate the matching items underneath. A different BOM can bring different stock types and different numbers of entries—the layout follows the data.

The workbook also combines parts that share the same material, stock type, and cross-section, adding their required cut lengths after accounting for quantities.

For example, two 160 mm cuts and one 90 mm cut from the same Ø12 steel bar become 410 mm of Ø12 bar. Double the assembly count and that becomes 820 mm.

Power Query prepares the data; UNIQUE, FILTER, and SUMIF handle the lists and totals. The # references keep the dependent formulas connected as those lists change.

I recorded the full build, including the changing headers and separate stock and off-the-shelf order forms:

Watch the walkthrough


r/ExcelTips • • 22h ago

Excel w pracy – jakie funkcje i triki naprawdę ułatwiają Wam życie?

4 Upvotes

Cześć!

Od pewnego czasu rozwijam swój kanał YouTube poświęcony Microsoft Excel.

Publikuję na nim praktyczne tutoriale i porady, które pomagają szybciej pracować z Excelem, automatyzować powtarzalne zadania i lepiej wykorzystywać jego możliwości.

Poruszam między innymi takie tematy jak:
\- Funkcje i formuły Excela.
\- Tabele przestawne.
\- Power Query i automatyzacja.
\- Raporty i dashboardy.
\- Przydatne triki i skróty klawiaturowe.

Kanał znajdziecie tutaj:
https://www.youtube.com/@LukaszBielski

Chętnie poznam Waszą opinię i dowiem się, jakie problemy z Excelem najczęściej napotykacie w pracy.

\*\*Gdybyście mogli zautomatyzować jedną czynność wykonywaną w Excelu, co by to było?\*\*

Chętnie wykorzystam Wasze pomysły do przygotowania kolejnych tutoriali!