Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.
If you need to confirm a number is a whole number you can use a function with a short name.
A beginning is the time for taking the most delicate care that the balances are correct.
Frank Herbert (1920-1986) the start of Dune
Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.
If you have the subscription version of Excel you may have the new LET function. This function lets you capture variables within a formula. Let’s see an example.
I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
Macros are designed to reduce keystrokes and mouse clicks. Here is a perfect example. It takes seven separate mouse clicks to use an Outlook email template as a new email. Here is a macro that does it in one.
The Goal Seek feature can save you a lot of trial and error when you want a calculated cell to equal a specific value.
The keyboard shortcut to open the Goal Seek dialog is Alt T G.
One of the most powerful statements in VBA is Selection. This holds whatever the user has selected just before they ran the macro. Normally it is a range, but what if it isn’t?
Shortcut to lock or unlock a cell
The keyboard shortcut to unlock or lock a cell or a range (its a toggle, so it switches between the two) is Alt H O L pressed in sequence, not held down.
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
Did you know you can accumulate copied entries in the Windows clipboard?
The default setting is to only have the last thing you copied, but a setting change can give you access to multiple items to paste.
You can also pin items to the clipboard to keep them there.
To change the setting.
Click the Windows button and choose the Settings icon
The select System.
Then select Clipboard (bottom left) and change the Clipboard History to On – done.
If you copy multiple items you can press the Windows key and V to access what’s on the clipboard.
Then you can click the top right elipse icon to Pin the item to the clipboard.
Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
Nothing in life is to be feared, it is only to be understood. Now is the time to understand more, so that we may fear less.
Marie Curie (1867 – 1934)
Export as PDF
If you frequently export files as pdfs then this keyboard shortcut may save you a little time.
Pressed in sequence, not held down use
Alt F E A
Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post.
In a For Next loop you don’t have to include the variable in the Next statement. But ….
When working with weeks in Excel you may need to show the start and end date of the week in the same cell. Here’s how you can do that.
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.
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.
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.
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.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
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)
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.