More Excel tips from John Michaloudis

by | May 13, 2021

Welcome to part two of our blog series on essential Excel tips, where you’ll learn 5 more time-saving tips from John Michaloudis — Chief Inspirational Officer at MyExcelOnline. And as before, we’ll finish up with how you can use your Excel spreadsheets for interactive content to ensure your Excel data is presented in the best possible light for easy analysis.

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

 

How to check your math with F9 in Excel

 

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.

Use F9 to see a sum within a 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.

Separating different parts of a formula in Excel

 

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

 

How to remove duplicates in Excel

 

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.

How to put your data in a table in Excel

Once your data is in a table, duplicates can easily be removed from columns.

 

Tip 8: Table row calculations

 

How to do table row calculations in Excel

 

It’s good practice to have your Total Row option checked to get a totals row at the bottom of your table.

How to use the Total Row option in Excel

 

If you right-click in a cell in the Totals Row you have access to lots of formulas.

How to access formulas in Total Rows in Excel

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

 

How to create a data entry form

 

The new form Dialogue box looks like this. It’s where you fill in your data:

Using the Form Dialogue box in Excel

 

Press New and it will fill in your form:

Creating a data entry form with Dialogue box in Excel

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

 

How to 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:

How to use Grouping in Report Filter Pages in Excel

 

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.

How to set a Running Total in Show Values As option in Excel

 

Now it will display the Running Total In or YTD.

Running Total in Totals Row

 

Bonus tip: To get customers into individual tabs use Pivot Table Analyse, found in the menu bar.

Pivot Table Analyse in Excel

 

Then on the far left, click on the Options drop-down:

Using the Pivot Table 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).

Using Show Report Filter Pages in Excel

 

When you press OK, customers will have their own individual tabs at the bottom.

Set up individual customer tabs in Excel

 

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.

Customising components in Squirrel365

 

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.