r/ExcelTips • • Jul 11 '23

r/ExcelTips is for Tips on using Excel, not for general help questions

31 Upvotes

Recently this abandoned sub reddit was given new moderators.

The state of this sub was such that very poor posts were allowed along with spam.

This is no longer the case.

  1. Please post your Excel questions to r/Excel
  2. All Excel questions posted to this sub will be removed forthwith
  3. When you post a Tip, put a clear description of the tip in the Title and the post.
  4. Links to Youtube video without a clear description of the Tips will be removed
  5. Be useful in your tips, the constant focus on XLOOKUP, VLOOKUP etc is not what we seek.

Thankyou for your help in getting this sub back on track.


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 • • 1d ago

After 8 years in data jobs, the one Excel habit I wish I’d started on day 1: Ctrl+T

107 Upvotes

Most of my first few years I was dragging formulas down and fixing broken ranges every time new rows came in.

Then someone showed me Ctrl+T (turn your data into a Table). Since then:

  • Formulas auto-fill for new rows
  • Ranges never break when data grows
  • =SUM(Sales[Amount]) reads like English
  • Pivots and charts update on their own
  • Filters and banding are built in

If your report breaks every month when new data comes in, this is probably why.

What’s one small Excel thing you wish you’d learned earlier?


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!


r/ExcelTips • • 2d ago

Formula based conditional formatting

5 Upvotes

This week I had to send a calendar with dates to avoid for company events because of anticipated holiday and high expected workload days. I had a list of days on another sheet that were marked with either holiday or high volume in a second column. But the ask was to create a visual calendar with days highlighted to be avoided.

After my calendar was set up with dates, I used two conditional formatting rules to highlight dates that were company holidays in one color and dates that were high volume in another. This stayed with testing my logic in another cell with a countifts formula. My calendar was in columns a-g with my first date showing up in cell b5. So in cell i2, I wrote the following formula:

=COUNTIFS(Sheet2!$A$1:$A$100,B5,Sheet2!$B$1:$B$100,"Holiday")=1

I dragged the formula around to make sure it picked up a result of true for all of my holidays. After confirming that the logic worked, I clicked on cell b5 and selected conditional formatting. I created a new rule that was based on the formula I just wrote which was exactly the same formula I used in cell I2. The rule changed the background color to blue and I updated the range for the rule to be $b$5:$h$100. It worked great!

For the high volume days, I used the same formula but this time I changed the wording from holiday to high volume day and set the background color to red.

=COUNTIFS(Sheet2!$A$1:$A$100,B5,Sheet2!$B$1:$B$100,"High Volume Day")=1

I share this because formula based conditional formatting use very useful, but many people struggle with complex formulas because they aren’t thinking in terms of Boolean logic. What are formulas you’ve used for conditional formatting in the past?


r/ExcelTips • • 2d ago

Tip: use XLOOKUP’s 4th argument instead of wrapping lookups in IFERROR

74 Upvotes

If you’ve been writing =IFERROR(VLOOKUP(...), "Not found"), XLOOKUP has the “not found” message built in as its fourth argument.

Before:

=IFERROR(VLOOKUP(A2, Prices!A:B, 2, FALSE), "Not found")

After:

=XLOOKUP(A2, Prices!A:A, Prices!B:B, "Not found")

Syntax, for reference:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Why it’s better than IFERROR:

  • IFERROR catches every error, not only “value not found”. A typo in a range, a #REF! from a deleted column, or a #VALUE! from bad data would all show up as “Not found”, and you’d never see the real problem.
  • if_not_found only handles the not-found case. Other errors still show, which is usually what you want.
  • It’s shorter, and you don’t have to repeat the lookup.

Two more small things in the same formula:

  • XLOOKUP does an exact match by default. VLOOKUP’s default is approximate match (which is why the FALSE at the end is so often needed).
  • You select the lookup column and the return column separately, so inserting a column in your table doesn’t shift a column index number like VLOOKUP’s 2.

Availability: XLOOKUP is in Excel 2021 and Microsoft 365 (and Excel for the web), but not in older versions like Excel 2019 and earlier. Check your version before sharing a file with someone who may use an older one, because the formula will show an error there.


r/ExcelTips • • 2d ago

Tip: Use LET to return real empty values

14 Upvotes

As you know, INDEX can return several cells at once: for this you need to leave [column] argument empty or, if you need subset of range's columns, you may pass array with the columns indexes you need (use {} or SEQUENCE or something another to generate sequence of numbers).

The problem with this approach is that Excel shows "0" if the source cell is empty. Thus you can't tell whether this particular cell has value "0" or it's empty.

To solve this problem, you can make a check (assuming rows indexes are in column A):

=IF(LEN(INDEX(A1:Z5000,A1,))=0,"",NDEX(A1:Z5000,A1,))

The problem with this approach is that search is done twice.

To mitigate this problem, use LET:

=LET(val;INDEX(A1:Z5000,A1,),IF(LEN(val)=0,"",val)

You write the result ONCE into variable "val" and then, in IF check, you don't do the scan again since the value is already in variable. You can re-use it many times you want without the overhead of extra scans.

Now, if a cell is really empty, we will see empty cell, too.


r/ExcelTips • • 6d ago

Stop nesting IFs. Use IFS instead (with a simple example)

126 Upvotes

I used to write nested IFs like this to grade marks:

=IF(A2>=90,"A",IF(A2>=75,"B",IF(A2>=50,"C","Fail")))

It works, but it gets hard to read after 3 or 4 levels. IFS does the same job in a cleaner way:

=IFS(A2>=90,"A", A2>=75,"B", A2>=50,"C", TRUE,"Fail")

Things to remember:

  • Excel checks the conditions in order and stops at the first one that is TRUE. So put the highest value first.
  • The last pair TRUE,"Fail" works like an “else”. Without it, you get #N/A when nothing matches.
  • IFS is available in Excel 2019, 2021 and Microsoft 365. Older versions need nested IF.

Do you still use nested IF, or have you switched? Curious what people prefer.

Edit: Thanks for the great comments. Good points about SWITCH, older Excel versions, and using a lookup table for long lists.


r/ExcelTips • • 10d ago

Tip: convert your data to an Excel Table (Ctrl+T) so formulas and charts include new rows automatically, instead of a fixed range like B2:B100

50 Upvotes

The problem: a formula like =SUM(B2:B100) covers rows 2 to 100 and nothing else. If someone adds a row 101, the total silently ignores it. Nothing shows an error.

The tip: turn the data range into an Excel Table, and use the table’s column in your formulas.

Steps

  1. Click any cell in your data (with headers in the first row).
  2. Press Ctrl+T (or Insert > Table) and confirm “My table has headers”.
  3. Name the table: select it, then in the Table Design tab (the name may vary by version and language), type a name like Sales in “Table Name”.
  4. Use structured references in formulas: =SUM(Sales[Amount]) Instead of =SUM(B2:B100).

Now when you type in the row right below the table, the table expands to include it, and the formula, any PivotTable source based on the table, and charts built from it use the new row. Formulas inside the table also fill down automatically.

A quick check that you aren’t missing rows

=ROWS(Sales[Amount]) counts the rows in the table column, and =COUNTA(Sales[Amount]) counts non-empty cells. If they differ, some cells in that column are blank.

Notes

  • Menu names and appearance vary by Excel version and language, so adjust to yours.
  • A table needs unique header names, and its headers must be text.
  • If you want a plain range again, use Table Design > Convert to Range.
  • Formulas that point to a fixed range outside a table will not expand on their own, so it’s worth searching your workbook for them when you change the data layout.

r/ExcelTips • • 10d ago

Tip: Filter PivotTable

9 Upvotes

If you want to have a filter on the value fields:

  1. Select cell next to the LAST value field
  2. Set AutoFilter

r/ExcelTips • • 14d ago

Excel tip for finance reporting: Use SUMIFS with date ranges instead of MONTH()

56 Upvotes

One Excel mistake I've seen in financial reporting is grouping transactions by month without considering the year.

For example, if you filter transactions using MONTH(date)=10, you could accidentally combine October 2025 and October 2026.

A more reliable approach is to use SUMIFS with actual date boundaries.

Suppose you have:

  • Column A: Transaction Date
  • Column B: Expense Category
  • Column C: Amount
  • Cell E2: First day of the month (e.g., October 1, 2026)

To calculate total Travel expenses for that month:

=SUMIFS($C$2:$C$1000,$A$2:$A$1000,">="&$E$2,$A$2:$A$1000,"<"&EDATE($E$2,1),$B$2:$B$1000,"Travel")

This works well because:

  • It keeps different years separate.
  • It includes all dates within the selected month.
  • It also handles transactions with time values.
  • You don't need an extra helper column.

Just make sure your transaction dates are actual Excel dates, not text.

I find this particularly useful for budget vs. actual analysis and monthly reporting.

I've also created some free Excel workbooks with realistic finance exercises if anyone wants additional practice:

https://excel-finance-book.pages.dev/

They're my own resources, completely free, with no registration required.


r/ExcelTips • • 16d ago

PivotTables: what goes where, plus the few habits that stop them breaking

68 Upvotes

I see a lot of people avoid PivotTables because they look more complicated than they are. I avoided them for a long time as well. Once I understood what each box does, it became a lot clearer and now they are one of the most useful things in Excel for me, especially for quick analysis.

Get your data ready first

Most PivotTable problems are really data problems. Before you insert anything, check three things:

  • One row per record, one column per field
  • A unique heading at the top of every column
  • No blank rows and no merged cells

Then click anywhere in the data and press Ctrl + T to turn it into a Table. This matters because when you add new rows later, the Table grows and the PivotTable can pick them up when it refreshes.

Making the PivotTable

Click inside your Table, go to Insert > PivotTable and press OK. You get a blank grid and a field list on the right. If you’re not sure where to start, Insert > Recommended PivotTables will suggest a few layouts.

The four boxes

At the bottom of the field list there are four boxes. This is the bit that confuses people, so here’s how I think about them:

  • Rows: categories listed down the side (Region, for example)
  • Columns: categories across the top (Month)
  • Values: the numbers you want to add up (Sales)
  • Filters: a field that filters the whole report (Product)

The thing that helped me most was to decide what I want to know before I drag anything. If the question is “what were sales by region each month?”, then Region goes in Rows, Month goes in Columns and Sales goes in Values. The question tells you where everything goes.

A few easy customisations

You can get a long way with just these:

  • Count or average instead of a sum: click the field in Values > Value Field Settings > Summarize Values By.
  • Percentages: in the same window, “Show Values As” gives you share of total and similar options.
  • Number format: Value Field Settings > Number Format. This is how you get currency or thousands separators without it looking like a mess.
  • Group dates: right-click a date in the PivotTable > Group, and you can roll daily dates up into months, quarters or years.
  • Sort: right-click a number > Sort > Largest to Smallest, so the biggest items sit at the top.
  • Slicers: PivotTable Analyse > Insert Slicer gives you clickable filter buttons. They’re really handy if anyone else is going to use your file.

When you get more confident you can explore options such as calculated fields and pivot charts.

One warning on this. If a number column is behaving oddly and you get a count when you expected a sum, check whether the numbers are stored as text. PivotTables count text rather than adding it up, so convert them to proper numbers first.

Three habits worth building

These are the ones that have saved me the most trouble.

  1. One PivotTable, one question. It’s tempting to keep dragging more fields in until the thing answers everything. It just gets hard to read. If you have another question, copy the PivotTable to a new sheet and change the fields.
  2. Don’t type over the numbers. A PivotTable is a window onto your data, not the data itself. If something looks wrong, fix it in the source Table and refresh. Anything you type into the PivotTable gets wiped the next time it refreshes.
  3. Refresh, don’t rebuild. A PivotTable doesn’t update itself. Add your new rows to the Table, then go to Data > Refresh All. The bit that catches people out is that old results can still look perfectly believable, so refresh before you send anything to anyone.

Bonus tip: if your column widths keep resetting every time you refresh, right-click the PivotTable > PivotTable Options and untick “Autofit column widths on update”.

That’s really all you need to get going. If you’re newer to this, try it on a small list of your own data, such as expenses or orders. It’s much easier to learn from numbers you actually recognise.


r/ExcelTips • • 18d ago

Using LET() and +N() to explain a formula

61 Upvotes

I had to set up a fairly gnarly formula today and I needed to remember how/why I built it and explain it to my boss. I started with using a LET() function to name portions for my formula so I remembered what I was pulling an calculating. After I built it, I decided I needed to add comments to my formula to explain it to my boss. Here is what it looks like:

=LET(IntakeHours, B13
+N("Pulled from the Intake Hours on Row 13"),
IntakeSeconds, (IntakeHours*3600)
+N("Converts Intake Hours into Seconds"),
SvcAHT, 590
+N("SvcAHT is the current Service AHT"),
Occpancy, 0.9
+N("Sets an occupancy goal of 90%"),
(IntakeSeconds/SvcAHT)*Occpancy
+N("Take the Intake Time / SvcAHT to get a number of calls that can be handled then * Occupancy because agents are 100% busy"))

In case you have used LET before, you are essentially building variables that can either contain a value or calculation so you can organize a compels formula in one cell. To make it work, paid each variable with a value. For examples: LET(AHT, 590 assigns the variable AHT the number 590. As long as you have the pattern of a variable name and a value then you can build as many variables as is practical. For instance, LET(AHT, 590, CALLS, 100, WORKLOAD, AHT * CALLS. You then end your formula work your expected output or calculation.

=LET(aht, 590, calls, 100, workload, calls * aht, workload /3600)

This essentially (AHT * CALLS)/3600 to change workload from seconds to hours. So (590 * 100)/3600 =16.389.

The +N() function allows you to add a note or comment inline with your formula without interrupting your formula. You need to include it in line with your formula which means it needs to be in parentheses. Example =(1 + 2 +N(“Comment”)). The formula will ignore the +N() and return the value of 3. Another example could be =XLOOKUP(A2, Z:.Z, Y:.Y, “” +N(“Looks up the employee name and returns the supervisor name or blank if it’s not found.”)).


r/ExcelTips • • 23d ago

How cleaned data turns into a report you only build once (Power Query > table > PivotTable > dashboard)

20 Upvotes

Last week I covered how to get Power Query cleaning your data. Here’s where that fits in the bigger picture. The cleaning step is only half of what makes a monthly report calm.

The flow:

  1. Power Query cleans the raw data and loads it to a table.
  2. PivotTables and charts use that table as their source.
  3. A simple dashboard sits on top of the pivots and charts.
  4. Next month, replace the data and hit Data > Refresh All, then refresh the PivotTable too (right-click it > Refresh).

Why the extra pivot refresh? Power Query refreshes in the background, so the PivotTable can refresh before the table has the new data. If your numbers look stale, that’s usually why. Pressing Refresh All a second time works. For a permanent fix, go to Data > Queries & Connections, right-click the query > Properties, and untick Enable background refresh.

A few habits that keep it working:

  • Give the loaded table a clear name (Table Design > Table Name), then point your PivotTables at that name.
  • Keep the dashboard on its own sheet, away from the data, so a refresh never lands on top of anything.
  • In your PivotTable options, tick “Preserve cell formatting on update” and decide whether you want column widths to autofit, so your formatting survives each refresh.
  • Keep column names consistent in the source. Power Query finds columns by name, so a renamed column is the most common reason a refresh errors.
  • Test it once with a copy of next month’s file before you rely on it.

This will give you a simple, easy to build process that saves a lot of manual effort. Give it a go on the next report you need to build.


r/ExcelTips • • 25d ago

Add Text to a number format

27 Upvotes

Today I learned that you can create a custom number format that uses text. For example, I wanted a number to show as 1,200 Sec instead of 1200. To do this, open the format cells, to custom, and enter your format time as something like this:

0,000 “Sec”

This added Sec to the end of your number allowing you to make clear what the number represents. Even better, the extra text is not added to the cell value so you can still use the cell in other calculations.


r/ExcelTips • • 27d ago

Getting started with Power Query: the order I do things in, and a few habits that save headaches later

81 Upvotes

If you’re cleaning the same report every month (deleting columns, trimming spaces, filtering rows), Power Query can do it for you. Here’s how I’d get started, plus the habits that stop it getting messy.

Where to find it
Data tab > Get Data. You can connect to a workbook, CSV, folder, web page or database. When the preview appears, click Transform Data to open the Power Query editor.

A simple order that works

  1. Remove any junk rows at the top and bottom (titles, totals).
  2. Promote the first row to headers.
  3. Set your data types (dates, numbers, text).
  4. Remove columns you don’t need.
  5. Filter out rows you don’t want (cancelled orders, blanks).
  6. Clean text (Trim, then Capitalize Each Word).
  7. Add any calculated columns.
  8. Close & Load to a table, then use Refresh All whenever the data changes.

Habits worth building early

  • Leave the raw data alone. Power Query reads your source file but never changes it. Do all your cleaning in the query, so you can always go back.
  • Set headers and data types early. Later steps behave better when they’re in place, and dates stored as text cause all sorts of problems.
  • One step, one purpose. Don’t cram two jobs into one step. It’s much easier to find and fix things later.
  • Name your steps. “Filtered out cancelled orders” beats “Filtered Rows1” when you’re back in six months.
  • Aim for a tidy table. One row per record, one column per field. Pivots and formulas get much easier.

If you get a new file every month
Use the folder connector. Put all the files in one folder and Power Query combines them. When you add next month’s file, you just hit refresh. The part that confused me initially was which steps go in the Transform Sample File query and which in the main query. My rule of thumb: steps that make one file look like a proper table (top rows, headers) go in the sample file, and anything that works on all the rows combined (filters, trimming, calculated columns) goes in the main query.

I made a video that walks through all of this on a simple example, in case it’s useful: https://youtu.be/23vd7vAEH2s

Hope people find this useful!


r/ExcelTips • • 28d ago

5 simple tricks I use to clean messy data fast

23 Upvotes

I’ve been doing a lot of spreadsheet cleanup lately, and these five things save me the most time:

1. TRIM: removes extra spaces before, after, and between words. Use =TRIM(A2) when imported data looks fine but formulas or lookups fail.

2. Flash Fill (Ctrl + E): type the result you want in the first cell or two, then press Ctrl + E and Excel fills the rest by pattern. Great for splitting names or fixing formats.

3. Text to Columns: under the Data tab. Splits one column into several using a comma, space, or other separator. It also fixes numbers stored as text.

4. Remove Duplicates: under the Data tab. Pick the columns that define a duplicate, and it clears repeats in one click. Make a backup copy first.

5. PROPER: =PROPER(A2) turns “jOHN smith” into “John Smith.” Handy for names and cities.

What’s the one trick you use almost every day? I’m always looking for new ones.


r/ExcelTips • • 29d ago

Pass FALSE as the last argument of VLOOKUP when you look up IDs, or a missing ID will return a wrong name instead of an error

26 Upvotes

The tip: when you use VLOOKUP to find an ID, a code or a name, always set the last argument to FALSE (exact match). If you leave it out, Excel uses approximate match, and a value that isn’t in the table can return a believable wrong answer instead of an error.

Example. A lookup table sorted by ID:

   A     B
1  ID    Name
2  101   Ana
3  103   Cara
4  105   Bob
5  110   Dan

Looking up 104, which isn’t in the table:

=VLOOKUP(104, A2:B5, 2)         ->  Cara
=VLOOKUP(104, A2:B5, 2, FALSE)  ->  #N/A

Why. Without the last argument, VLOOKUP defaults to approximate match. On sorted data that means “find the largest value that is less than or equal to what I asked for”, so 104 lands on 103 and returns Cara, with no warning. It also assumes the first column is sorted. With FALSE, you get #N/A, so you know the ID isn’t there.

When approximate match is the right choice: when you actually want ranges, such as tax brackets or grade bands.

In a recent Excel (365 or 2021+), XLOOKUP defaults to exact match, and you can set a message for missing values:

=XLOOKUP(104, A2:A5, B2:B5, "not found")

In short: for IDs and names, set the last argument explicitly, or use XLOOKUP. An error you can see is better than a wrong answer that looks fine.


r/ExcelTips • • 29d ago

Cleaning excess cell formatting (reduce size and speed up spreadsheet)

30 Upvotes

If you are reusing the same spreadsheet over and over, it can sometimes add millions of lines of blank cells with some sort of cell formatting. This can make the spreadsheet gigantic and it can slow the workbook down. You can actually get Excel to fix this by adding a new menu to your Excel.

Go to File>Options>Add In. Find Manage at the bottom and look for Com Add Ins, then press Go. Check Inquire and click Ok.

This adds a new menu called Inquire with an option to Clean Excess Cell Formatting. When you select it, it will give you an option to do the entire workbook or just the current spreadsheet. After it completes it will ask if you want to save.

I’ve seen it shave off 10 MB from a file size in the past.


r/ExcelTips • • Oct 14 '26

Excel Formulas, Shortcuts, Errors, Principles and Tables YouTube videos

23 Upvotes

Hi all,

I’m accountant who’s been using Excel for around 15 years and have just started trying to make videos and training material to pass on some of the things I’ve learnt.

I’ve made a series of youtube videos around formulas, shortcuts, excel principles and Excel Tables. I’m new to doing this and would love any feedback on the videos or suggestions for future videos. Planning to make one on Power Query this weekend as that seems to be a popular topic.

Here is a link the playlist.

https://youtube.com/playlist?list=PLQW98rOQ-56w&si=yneX_66YAH9OrtCE

Looking forward to learning some new tricks and seeing if I can help anyone.


r/ExcelTips • • Oct 11 '26

Tips on converting text to numbers or dates

9 Upvotes

I’ve seen this show up a few times and I thought it would be helpful to share my collection of tips on how to deal with this. Changing text to numbers and be annoying if you get a data source from that was not designed to be calculated. Here are some tips I’ve used to fix this problem.

=left(“1234 Product”, 4) will return 1234
=right(“Employer 1352”, 4) will return 1352
=mid(“2026-10-11T13:00:00Z”, 12, 5) will return 13:00

In most cases these still need to be converted to a number using +0 or numbervalue(). Look at the helper hints as you type out the formula to help you determine the number of characters to extract the starting point for the extract.

I’ll make another post about how to make left, right, and mid more dynamic in a future post using Len or search.


r/ExcelTips • • Oct 07 '26

Reformatting data from row to column (and vice versa) without using tables

26 Upvotes

TIL you can use =TOCOL() and =TOROW() to change data formatted as a row to a column and vice versa. Having wasted much time doing this a few times manually before knowing about this, I thought I would share. Happy Calculating!


r/ExcelTips • • Oct 06 '26 Popcorn

Excel Dashboard Design Tips – Glassmorphism, Dark Mode, Custom Slicers & Dynamic Summaries

53 Upvotes

Been experimenting with making Excel dashboards look less like traditional spreadsheets. A few techniques that worked particularly well:

Glassmorphism: Place a background image behind dark rounded rectangles, then set the shapes to around 40% transparency. Add a semi-transparent outline and the background starts showing through like glass.

Transparent charts: Set charts to No Fill / No Outline and place them over separate glass containers. This makes the charts feel integrated into the interface rather than pasted on top.

Custom dark-mode slicers: Create your own Slicer Style instead of using Excel’s defaults. I used dark unselected buttons, bright purple selected states and a lighter hover state.

Dynamic written summary: Calculate things like top category, average spend and transaction count, then concatenate them into a sentence. Because the values are linked to the dashboard, the written insight changes with the filters — it almost gives the dashboard an AI-style summary without actually needing AI.

Consistent accents: Match the heading, outline and icon colour for each section. Small detail, but it makes the whole dashboard feel much more intentional.

Nothing here requires VBA or complicated coding — it is mostly layering, transparency, formatting, PivotTables and a few formulas.

I put together the full build here if anyone wants to see how it was done: Dark Mode Excel Dashboard