Hey Friends,
Welcome back to the Sheet!
Here’s what to expect in today’s edition:
📋 Summary:
😱 Solving your challenges (Send me an Excel problem you’re facing)
💡 The Sheet Tip (Record your macro ⚡)
And…in case you missed it last time, download our shortcut guide using this link 😀.
=😱 Solving your challenges (Send me an Excel problem you’re facing)
Are you struggling with a specific Excel challenge at work? Send it in and I’ll solve it for you and share the results in the next edition of The Sheet 📃
➡️ Submit your tricky problems here ⬅️
=💡 The Sheet Tip (Record your macro ⚡)
It’s that time again, with month-end looming your boss has asked you to format the revised profit data. You have to carry out the same boring and time-consuming formatting each month.
Sound familiar?
We’ve all been there. I’m here to share some good news 😎- If you know how Excel can automate this for you 🤖 using macros.
Let’s dive in…
Step 1 - Enable the ‘Developer’ tab (it sounds fancier than it is)
First off you will need to ensure you enable the ‘developer’ tab in your Excel Ribbon. To do this go to File ➡️ Options ➡️Customise Ribbon ➡️ Ensure ‘Developer’ is ticked on the right-hand side ➡️ Hit OK
Step 2 - Record your Macro
Next, you need to go over to the ‘Developer’ tab in the ribbon and click Record Macro.
This will prompt you to name your macro. Type something sensible and hit ok.
Please note your name cannot contain any spaces!
Here’s where the magic happens. You can go about formatting your table as you normally would while the macro is recording. In this example, I changed the background and text colour of the header row, bolded the header row, changed the formatting of the cells, and even added some conditional formatting.
Pro tip 📎- When selecting data use the CTRL + ARROW shortcuts e.g. CTRL + DOWN arrow was used to select the date column above. Doing this means your macro will work even when the size of your data changes, for example if more rows are added.
Once you’ve finished your formatting go back to the ‘Developer’ tab and click Stop Recording
Step 3 - Running your macro on a ‘new’ data set
You’ve now got a set of new data in a new sheet in your workbook to apply your formatting to…
Go to the ‘Developer’ tab ➡️ Click on ‘Macros’ ➡️ Select the macro you want ➡️ hit ‘Run’
Now Sit back and watch the magic happen 🪄
Go on….give it a go
That’s it for this week.
Don’t forget to send me your Excel problems
The Functional Excel Team