Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DominikPetri
Advocate V
Advocate V

How to use your Office Theme as Power BI Report Theme

With Report Themes (preview feature in Power BI Desktop) you can apply a color theme to your entire report, such as corporate colors.

 

Do you already have a corporate color palette in Microsoft Office (Word, PowerPoint, Excel etc.) and want to use it Power BI Desktop? I have created an Excel File that converts the Office Theme Colors into a Power BI Desktop Theme in 4 simple steps:

 

  1. Select the Office Theme Color in Excel from the Page Layout tab
  2. The embedded VBA code will create the hex codes for the color palette
  3. Name your theme
  4. Preview the theme and save it (via dialog) in your preferred folder

You can download the file from my OneDrive (since it contains forms and VBA you can't run it in Excel Online):

Office Theme Converter v3 as zip file

 

If you have any suggestions, improvements, comments etc. please let me know 🙂

 

Thank you and have fun converting your existing Office Design Colors to Power BI Desktop!

 

Dominik Petri

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘
1 ACCEPTED SOLUTION

Hi all,

 

you can download the updated version in which I fixed the hex codes bug here:

Office Theme Converter v3 as zip file

 

Let me know what you think!

 

Thanks

Dominik Petri.

 

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘

View solution in original post

23 REPLIES 23
Magsy2000
New Member

Why is it, that when opened in Excel there is no Marco assigned to the Convert button?

The macros are definitely in the file and assigned to the button. I suspect that due to your security settings the macros got disabled...

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘
Mr-Crispy
New Member

 
mrothschild
Continued Contributor
Continued Contributor

Thanks for sharing.  Worked perfectly!

severgreen
New Member

Newbie question - I have downloaded your file and converted my branding colors and saved as a JSON... But now what do I do with it? I know I can upload JSON files into PowerBI but I'm not sure that's the action to take. How do I actually get these colors inside my dashboard in Power BI?

Have a look here:

https://docs.microsoft.com/en-us/power-bi/desktop-report-themes

 

Kind regards,

Dominik.

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘

Thanks for posting this. Worked like a charm. 

Glad that you like it. Please give kudos to the original post 😉
Thank you! 🙂

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘
scavins
Regular Visitor

This macro worked perfectly. I didn't have to get the hex codes for all the colors in our corporate theme and make my own JSON.

SusanD
New Member

Hi

Sorry if this turns up as a duplicate, my first attempt seems to have been lost! I've got the workbook downloaded, but as soon as I click the button to generate the hex codes, the JSON preview cell just displays #NAME? this happens whether I have changed the theme or not, even with the theme that the book came with. It starts off ok, displaying a preview, but as soon as that hex button is clicked, the preview stops working. Click into the box and you can see the formula, but no sensible output?

Thanks!

Hi Susan,

 

I guess this is due to the fact that I used the TEXTJOIN function in Excel. This function is only available in the Office 365 Edition of Excel 2016.

 

You can download a version (zip file) here that does NOT use this function:

Office Theme Converter v2 as zip file

 

Hope this helps

Dominik.

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘

Great thank you, that's done the trick.

Very neat, but it looks like the Hex2Dec function returns the bytes in the wrong order. I believe the hex code should be the same as the web color code. If so, it seems like the first and third bytes are reversed. 

@npnigro your are completely right. I've already fixed this problem and will have the file changed by the admin shortly.

 

The VBA property RGB returns the hex code in the order BGR instead of RGB - which I didn't know but do know now 🙂

 

Best regards

Dominik.

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘

Hi all,

 

you can download the updated version in which I fixed the hex codes bug here:

Office Theme Converter v3 as zip file

 

Let me know what you think!

 

Thanks

Dominik Petri.

 

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘

Although it has been some time since you posted this, the tool you built is really cool!  Well done, @DominikPetri!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

dcanalesg
Regular Visitor

When I tried to download the Excel file from OneDrive I got the following message:

"Features that we can't show in the browser and interactive reports will be removed from the downloaded copy."

After I downloaded the file I had to insert the "Generate Hex-Codes for Colors" and "Save as JSON" buttons and assign the corresponding macros.

Because I use Excel 2013 and Textjoin is a new Excel 2016 formula I had to replace the third line of the formula on row 25 with:

CHAR(34) & "dataColors" & CHAR(34) & ": [ " & Accent_1 &","& Accent_2 &","& Accent_3 &","& Accent_4 &","& Accent_5 &","& Accent_6 &","& Hyperlink &","& Followed_Hyperlink & " ]," & CHAR(10) &

After that everything worked fine. Thanks!!!!

dcanalesg
Regular Visitor

When I tried to download the excel file from OneDrive I got the following message:

"Features that we can't show in the browser and interactive reports will be removed from the downloaded copy."

After I downloaded the Excel file I had to insert the "Generate Hex-Codes for Color" and the "Save as JSON" buttons, and assign the corresponding macros.

I have Excel 2013 and because Textjoin is a new Excel 2016 function I had to replace the third line of the formulas on row 25 with: 

CHAR(34) & "dataColors" & CHAR(34) & ": [ " & Accent_1 &","& Accent_2 &","& Accent_3 &","& Accent_4 &","& Accent_5 &","& Accent_6 &","& Hyperlink &","& Followed_Hyperlink & " ]," & CHAR(10) &

After that, everyting worked fine. Thanks!!!!

Hi @dcanalesg,

 

Glad that you like it. If you download the file to YOUR OneDrive first, the buttons should be there.

 

Nevertheless, thanks for posting your solution and your formula for those who use Excel 2013 (or Excel 2016 without Office 365) and therefore don't have TEXTJOIN.

 

I will (hopefully) soon finish a blog post with an alternate download link....and a version without TEXTJOIN 🙂

As soon as the blog post is online, I will post the link here.

 

Dominik.

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘

Hey Dominik,

 

I really like your idea and think it's very useful. Thanks a lot. 

 

Regards,

Lars

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.