Slicers are a great filter interface. Sometimes, due to layout restrictions, you prefer the slicer to go across the sheet rather than down the sheet. Here’s how you do that.
Percentage Area Chart in Excel
Conditional Format technique
You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.
Using SEQUENCE with Dates in Excel Part 3
How to highlight specific dates
In this post I finish off the Calendar matrix by adding holidays.
Restart a Pivot Table in one go
If you want to clear all the fields from a Pivot Table you can use the clear all option.
On the PivotTable Analyze or Analyze tab click the Clear drop down and select Clear All.
This removes all the fields and allows you to start again from scratch.
Using SEQUENCE with Dates in Excel Part 2
Time to enter the matrix
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
Financial Modelling with Scenarios
A great webinar recording from Danielle Stein Fairhurst from Plum Solutions and Thomas Paul from BDO.
A flexible technique to handle lots of different variables in a model using data tables.
Macro question – Slicer solution
Slicers are a very flexible filtering option and can control multiple pivots – even power pivots!
Always happy to help.
Using SEQUENCE with Dates in Excel Part 1
Date listing
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Time to Play with Dynamic Arrays
Let play time begin
Many things that were hard or complex are now much simpler. Creating dynamic drop down lists based on previous selections used to be tricky in Excel. Dynamic arrays make it straightforward.
The world is becoming like a lunatic asylum run by lunatics.
David Lloyd George (1863 – 1945)
Switching Rows and Columns is Now Easier
TRANSPOSE rules
With the introduction of Dynamic Arrays in all versions of Microsoft 365 (formerly Office 365) it is now a lot easier to use the TRANSPOSE function.
1. If it should exist. It doesn’t.
2. If it does exist, it’s out of date.
Arnold’s First and Second Laws of Documentation.
Excel Chart Title Hack
Linking to a range
I learned something new recently watching a recorded webinar by Jon Peltier. That’s not unusual, he is a charting legend.
Talent is cheaper than table salt. What separates the talented individual from the successful one is a lot of hard work.
Stephen King (1947 – )
The End of SUMPRODUCT
Dynamic arrays will carry it off in to the sunset
The SUMPRODUCT function has been my favourite function for about 20 years. It is so flexible. Soon it will be redundant thanks to dynamic arrays.
Don’t panic.
Douglas Adams (1952-2001)
The Hitchhikers Guide to the Galaxy
Linking text boxes
A trick to allow copying between sheets
Linking to a text box in a sheet is straightforward, unless you want to copy that linked text box to another sheet and retain the link. Here is how you do it.
Standard Technique
To link to a text box you click the text box and then click in the Formula Bar and press = and then click the cell to link to and press Enter.
This works OK on the sheet but if you copy the text box to another sheet it links to the same cell in the other sheet. If that’s what you want, great. If it isn’t then you need to use this technique.
Text box copy technique
Click the text box click in the Formula Bar and press = then instead of clicking on the current sheet click on another sheet tab and click a cell in another sheet then return to the current sheet and then click the cell you actually want to link to and press Enter.
By doing it this way the sheet name is included in the link and that ensures the link is kept when you copy the text box to another sheet.
You could also manually type the sheet name into the Formula Bar, but using the mouse is much easier.
You don’t have to burn books to destroy a culture . Just get people to stop reading them.
Ray Bradbury (1920 – 2012)
The Art of Possibility (2002)
A great book to help you think about things a little differently and hopefully act a little differently too.
Some great ideas and stories, highly recommend.
I listened to the audio book version which had the added benefit of music.
Adding a message to grouping icons
A SUBTOTAL trick
It is best practice to use grouping to hide and unhide rows in Excel. I recently saw a technique that also displays a message.