Excel pivot tables make it easy to analyze data quickly.

But... It can be even quicker - and more impactful - if you know where to click.

Also in Between the Sheets today:

• Summer Excel Challenge concluded
• custom number formatting in Power BI visuals
• update to MS Forms sync to Excel

🎬 Pivot Table Hidden Features

In this week's video, we're exploring some of the lesser-known features of pivot tables.

​

​Download the file to follow along.

Follow these tips to:

• Speed up pivot table creation
• Take advantage of in-built dynamic formatting to keep track of items (even when your layout changes)
• Use the hidden conditional formatting pivot table icon
• Go beyond standard number formatting options to get clean reports
• If - like me - you prefer the tabular layout, see how you can make it the default in all your future files. (Or outline. Anything but compact form 😋)
• Plus a hidden trick that can save you a ton of time if you have to prepare separate reports for different departments or managers. Most people don't know to look for it.

Watch the video and let me know which one excited you the most.

BTW, if you haven't signed up to the waiting list for the upcoming pivot table course yet, you can do so here.

​

🏆 Excel Summer Challenge Winners

The Summer Excel Challenge has concluded.

Thank you to everyone who participated.

Your dedication to improving your Excel skills is admirable.

We hope you found the challenge rewarding.

We've drawn (using Excel, of course 😉) 10 lucky winners. They will receive our upcoming Pivot Table Essentials - Basics to Mastery course for free.

Join us in congratulating Praveen, Fred, Yaw, Licínia, Paul, Emanuele, Klaus, Ella, Yvonne, and Guray.

We've already reached out to them.

The Challenge may be over, but the Exercise Pack is still available.

It offers extra practice - a chance to get better at Excel.

Here're some comments we've received:

We're always grateful for constructive feedback.

We're taking it on board for any future exercise packs we put together. 😊

​

🤓 Geeky News

📋 Update to the way MS Forms sync to Excel

Back in January, Microsoft introduced automatic sync of Forms to Excel.

Previously, this was only possible if you created the form directly from Excel Online.

Now those older synced Forms, created in Excel Online, will need updating.

When you open an Excel workbook that uses the older syncing solution, you'll see a pane on the right prompting you to "update sync".

If you fail to do this, the file will stop syncing new responses.

When you click on the "Update sync" button, Excel creates a new sheet in the same workbook.

It will resync all your previous responses to the new sheet and continue syncing live any new responses as well.

The original sheet will no longer be updated.

Make sure to follow these steps and update any reports that rely on live data coming from Forms before October 20th, 2024.

📊 Format values for each Power BI visual

When creating a Power BI report, the best practice is to create explicit measures and define their format.

But sometimes you want to show the values in a different format in a specific visual. Show the decimal value as an integer, or change the number of decimals shown, for example.

Now you can. August update to Power BI Desktop introduced visual level format strings.

Select the visual and go to Properties (if you're using on-Object formatting) or General (if you're a traditionalist).

Open Data Format > Format Options and specify the Format you want.

The logic is the same as in Excel's Custom Number Formatting.

First, you define the format for positive values, then negative values, then zeros. Separate each with semi-colon.

You can get creative. Even use symbols or emojis.

This functionality was added to Power BI to allow formatting visual calculations, which aren't stored in the model.

But it can be used with any measure, explicit and implicit alike.

To use visual level format strings, go to Options > Preview features and enable Visual calculations.

​

👏 Power Stories

Congrats to Bhavik for completing Master Excel Power Pivot & DAX (Beginner to Pro).

Bhavik is a very active student in many of our courses.

He frequently offers alternative approaches and generously shares his insight in the comments.

We like to see such engagement. The XelPlus Community is truly inspiring.

BTW, the upcoming Pivot Table Essentials - Basics to Mastery course is not a replacement for our Power Pivot & DAX course.

In Pivot Table Essentials, we focus on standard pivot tables.

If you want to get better at analyzing data but data modeling and DAX sound scary, Pivot Table Essentials has got you covered.

It's very beginner-friendly.

If you deal with large and complex data on a regular basis, then Power Pivot is worth looking into.

If you're already a data model expert, you might still pick up some pivot table design tricks from the new course. 😊

See you next week,

Leila

Want more?

▶️ Subscribe on YouTube​

🖇️ Follow us on LinkedIn​

🥇 Join 400,000+ students in our courses​

📣 Want to sponsor Between the Sheets? Get in touch here.

📨 If you were forwarded this message, you can get the free weekly email here.

This newsletter contains affiliate links, which give us a small commission on any purchase made at no cost to you. This helps us run Between the Sheets and bring you updates like this. Thank you for your support!