This blog provides a summarised version of the last 5 tips (read up on tips 1-5 in part one) shared by John in his webinar with Squirrel365. If you want to learn more tips from John, check out his book 101 Best Excel Tips and Tricks.
John’s time-saving top tips continued…
Tip 6: Check your math with F9
A favourite tip of John’s! Press F2 on your keyboard to open a formula in a cell. Then highlight a section of the formula within the cell and press F9. You’ll see the actual sum within the formula.
When you’re next auditing or writing a big formula and you don’t know how it works, use F9.
Mini-tip: To avoid hard-coding don’t press enter. Use ctrl + z to get out of it.
Another little tip for working with long formulas is to separate out the different parts of the formula to make it easier to look at and work with. Put a cursor in between two different parts and press alt + enter.
The formula still works, you’ll just make it look visibly better. It’s useful for nested IF formulas when you have lots of different IFs.
Tip 7: Remove duplicates
Removing duplicates is easy if your data is in a table. John says:
Tables are the best thing that have been introduced to excel.
They make working with data much easier and quicker. Even if you have three rows of data, John recommends using tables.
To put your data in a table you have to highlight the data and press ctrl + t. You’ll know if it’s a table because the ‘Table Design’ menu pops up in the menu bar.
Once your data is in a table, duplicates can easily be removed from columns.
Tip 8: Table row calculations
It’s good practice to have your Total Row option checked to get a totals row at the bottom of your table.
If you right-click in a cell in the Totals Row you have access to lots of formulas.
This saves you from putting in formulas manually. The selected formulas are structured references so you can add more data to your table and the formula will update automatically to include that new data.
Mini tip: If you want to copy and paste data from the previous row into a blank row below, press ctrl + d.
Tip 9: Create a data entry form
The new form Dialogue box looks like this. It’s where you fill in your data:
Press New and it will fill in your form:
You can repeat the process to create new rows of data in your table. It’s a fun way to put data into your form and it’s easy to edit.
Tip 10: Show report filter pages
Firstly, create a Pivot Table by clicking inside the table to make sure you have the right menu bar at the top. Then select Summarise with Pivot Table from the menu bar.
If you’re looking for further helpful resources on pivot tables, check out MyExcelOnline for tutorials and Pivot Table examples.
The latest version of Excel automatically groups row labels. You can ungroup by right-clicking or select Group to see what the grouping is and choose to group by a different category:
To do a running total of year-to-date, move your Sum of Sales (or equivalent) into the Values field area then go to Value Field settings. Select Running Total In from the Show Values As options, with the order date selected also.
Now it will display the Running Total In or YTD.
Bonus tip: To get customers into individual tabs use Pivot Table Analyse, found in the menu bar.
Then on the far left, click on the Options drop-down:
Select Show Report Filter Pages for Customer or other data set that you’ve added (Customer is showing here because we’ve previously added it to the Filters area).
When you press OK, customers will have their own individual tabs at the bottom.
If you then hold down shift and select various customer tabs, the changes you make to the one that’s open will carry across to all the selected sheets.
This is a great way to show data for individual customers which you can then share in a PDF.
Don’t forget, for time-saving tips 1-5 read 5 Essential Excel tips from John Michaloudis. Or you can watch the on-demand webinar where John walks you through all of his essential hints and tips.
How to build interactive content with your Excel skills
(This section is repeated from the first blog in this series 5 Essential Excel tips from John Michaloudis)
John advocates using Excel in conjunction with Squirrel365. Squirrel opens up a world of possibilities, enabling you to build interactive content with your Excel skills.
John has a financial model spreadsheet (from a MyExcelOnline financial modeling course) with various tabs for income, expense, drivers, KPIs… When he makes a change to the drivers sheet, for example, to update the amount of revenue, Excel updates the other sheets.
All good so far, but the frustration comes with the need to check the updates because it requires a bit of back-and-forth which can be slow work.
A better way
With Squirrel365 you can take your data and create an interactive dashboard.
John says:
You can create things in Squirrel without a need for advanced Excel skills (which you would need if you were creating it in Excel). In very little time you can produce interactive web content or a dashboard with all your data. And you can do things such as change the revenue and see what happens to the rest of your data.
Squirrel removes the need to scroll between different sheets in Excel and it’s great for doing forecasting budgets. You can improve the usability of a financial model spreadsheet by turning it into an interactive model which is much easier to use and understand.
You can use the sliders to change your revenue and other markers, and see the results on the graph and in the table and KPIs. And you can build your interactive content for PowerPoint or for the web.
Take your Excel skills to the next level
It’s easy to create calculators, dashboards, interactive visualisations, and web apps in Squirrel based purely on the Excel capabilities of spreadsheets like John’s. Visit the Squirrel Showcase to fire up your imagination and see what else you can build in Squirrel.
There is a wide range of how-to videos to get you started in Squirrel, but it all starts with importing your xlsx file.
With Squirrel, you don’t leave Excel behind, you bring it with you, leveraging your existing knowledge. You can make changes to your data and formulas as you go which you can then export back out so you have the latest version.
Once you’ve imported your spreadsheet it’s a matter of dragging and dropping components (charts, diagrams…) onto the canvas, customising them, and binding them to your data.
Once your data is in a chart, remarkable things can happen.
If you add a slider to your canvas, and bind it to a spreadsheet driver such as revenue growth, when you slide the slider back and forth to change the revenue it changes the output in the chart — you just bind the component to the spreadsheet logic to see the different outputs. It’s as simple as that.
You can custom format all of your charts and components and do things like bind your text in a Text Label to a colour specified in a cell in your spreadsheet. Or you can set a KPI to change colour depending on the formula you’ve given to your data. For example, you can tell Squirrel to make the KPI red if the user moves a slider to a numeric value below a certain percentage, or green if above it.
All this dynamic interactivity comes directly from the data in your spreadsheet.
Sharing your interactive content
Publishing your projects up to the cloud is easy with Squirrel’s Project Manager. And it’s immediately available to embed into PowerPoint or share as a web link.
The Squirrel add-in for PowerPoint lets you take the project ID from Squirrel, put it into PowerPoint, and then interact with the project as you would in Squirrel. So you can share the link, embed in Teams, or put it on your website.
There is an enormous amount of capability in terms of what you can create and how you share it.
Why not try Squirrel out for yourself! Download a free 30-day trial and see what you can do with your spreadsheet skills.
