In this blog post, let’s go the very basics of it all.
What is Power BI?
Power BI is a software to create and publish interactive, web-enabled reports & visualizations for your audience. You can use Power BI on a PC or web to create things. Once you publish a report (or few visuals), your audience can consume them by using any device – PCs, Macs, Web browsers, Apps on cell phone / tablets etc.
Here is a more detailed tutorial on Power BI.
Demo of a Power BI report & interactive experience:
How is Power BI different from Excel?
So what, even Excel can create interactive reports. But there are several crucial differences between Power BI and Excel.
- Power BI allows rich, immersive and interactive experiences out-of-box. You can click on a bar in bar chart & other visuals respond to the event and highlight or filter relevant data. You can show graphs & visuals that are very tricky (or impossible) to reproduce in Excel like maps, pictures and custom visuals.
- Power BI works with large data sets There is no artificial limit of 1mn rows in Power BI. You can hookup to a business data set and analyze any volume of data. The limit depends on what your computer (or Power BI server) can process.
- Share and read reports easily You can create reports in Power BI and share them in formats that are universal (ie browser pages or apps). This means, your boss need not have Excel or Power BI installed to enjoy the beautiful reports you create.
- Power BI is for story telling while Excel is for almost anything. We can use Excel to simulate pendulum motion, calculate Venus orbit, model a start-up business plan or many other things. Power BI is mainly for data analysis & story telling. If you try to replicate a large, intricate financial model or optimization problem with Power BI, you will either fail or suffer miserably. On the other-hand, if you use Power BI for making reports, running cool analysis algorithms (clustering, outlier detection, geo-spatial patterns etc.) you will wow your colleagues and bosses.
How to get Power BI?
Power BI is free for individual use. Just head over to PowerBI.com and download the free desktop application (or get Power BI app from Windows store)
If you want to share your reports and work as a team, then you need a paid Power BI plan. PowerBI.com has useful information about this.
Note: Power BI is updated frequently. If you install it as an app, then Windows will automatically update Power BI when there is a new version. If you use Power BI desktop thru normal install, then you need to update it once in a while to use new features.
What is Power Query?
Power Query is a data processing & mashup software. We can use Power Query to
- Connect to several types of data sources (databases, files, web pages, social media, APIs, cloud storage etc.)
- Bring and combine data (append, merge, join etc.) from various places
- Derive new columns of data
- Format, remove or reduce data
- Reshape data (transposing, grouping, pivoting, un-pivoting and other creative ways)
- Write formulas to do advanced manipulation of data
- Publish refreshable datasets
Here is a detailed tutorial on Power Query.
The output of Power Query can go to either Excel or Power BI. That is why Power Query is available in both of these software.
Think of Power Query as a strange (but super-helpful) combination of SQL, VBA, Excel formulas and pixie dust. It gives us (people working with data) freedom to focus on real problems than worrying about issues like:
- where is my data?
- Is the data clean?
- What about missing values
- What if everything I need is not in one place
- <insert your data pain here>
Demo of what Power Query can do:
How to get Power Query?
In Power BI:
Power Query is an part of Power BI. So there is no need to get Power Query. It is always there. Just click on “Get Data” button and you enter the Power Query world.
In Excel:
- Excel 2016 / Office 365: has Power Query by default. No need to get anything. Just go to Data ribbon and use the “Get & Transform data” options to set up Power Query connections.
- Excel 2013 & 2010: You can install free Power Query add-in. Just download it from Microsoft Power Query website and you are good to go. You may need to enable Power Query from COM add-ins in developer ribbon.
What is Power Pivot?
Power Pivot is a calculation engine for pivot tables. You can use Power Pivot to model complex data, set up relationships between tables, calculate things to be show in value field area of Pivot tables / pivot charts or visuals.
Think of Power Pivot as a calculation layer between your data and outputs. You can tell Power Pivot how you want your calculations done thru a language called as DAX and Power Pivot can give the answers. It is an extremely fast & scalable software.
We can use Power Pivot in either Excel or Power BI.
How to get Power Pivot?
In Power BI:
Power Pivot is an part of Power BI. So there is no need to get Power Pivot. It is always there. You can use various features of Power Pivot from Modeling ribbon and from data & relationship views.
In Excel:
The present Power Pivot availability and licensing model is more complex than DAX. Let me try to highlight the key points. A good place to check is where is Power Pivot page by Microsoft.
There are two kinds of Power Pivot for Excel.
- Power Pivot engine: this is necessary for calculating values in pivot tables. It is available in Excel 2013, 2016, Office 365 and future versions of Excel.
- Power Pivot creator: this is necessary for adding new kinds of measures, managing data model etc. This is currently available only in certain types of Excel (professional, professional plus versions). From Excel 2019, this will be available in all kinds of Excel.
- Excel 2016 / 2013 / Office 365 Pro versions: Power Pivot is available in professional & pro plus versions. No need to download anything. Just enable Power Pivot COM Add-in and you are good to go.
- Excel 2013 & 2010: You can download free Power Pivot add-in from Microsoft and install it to use Power Pivot.
How Power BI, Power Query and Power Pivot are related…
Here is a simple diagram explaining how these 3 powerful software are related.
Getting started with Power BI, Power Query and Power Pivot…
If this is the first time you have heard of any of these tools, I suggest checking out below tutorials.
- What is Power BI, a deep and clear introduction with full tutorial
- Getting started with Power Query – Introduction + video
- What is Power Pivot and how it can help you – Intro video
Additional resources to learn about these tools:
- Best Excel & Power BI books
- Excel School online course – Power Query & data modeling is covered
- What is Power Pivot & why you should care [Matt’s blog]
- Power Pivot tips & tricks from Ken [Excelguru blog]
- Power BI blog [Microsoft Power BI team]
30 Responses to “18 Tips to Make you an Excel Formatting Pro”
For my 2 cents worth:
Less is more !
Keep styles simple and in line with the corporate requirements of your employer/client
The table formatting is really useful, but I have found two sticky points:
1. Cannot move or copy a sheet with a table in it.
2. Cannot 'table format' multiple sheets at once.
May be ways around these issues, but these are what keep me from using the table format more than I already do.
Remove gridlines in sheet
Use dotted lines as internal borders in tables
And just keep it simple - it's the substance that matters and there's already way too much eye candy out there
I write a lot of financial reports conveying complex data in a userfriendly manner. I don't use colour (as it costs 7p/sheet verses B/W at 1p/sheet). The trick is to generate a table that someone will skim over for "the story" and then can refer back to understand it. very muck like Ulrik said, keep it simple.
Some simple guidelines that I use:
(a) align headings based on data (if data is text that means left, if data is numbers that means right)
(b) do not align central numbers (unless all similar) i.e. how hard is it to read a column of numbers that contains €1.25 and €125
(c) use borders to group columns and rows, don't format every line/column but allow the data to draw your eyes along it. "White lines" are as useful as borders
(d) thin borders are better than fat borders - the fatter they are, the more they draw the eye... so use them to draw attention to key numbers (like a total) only.
(e) use units to make numbers easier to read. Generally people cannot skim numbers with more than 3 d.p or 5 significant figures. so report in millions/thousands (or the other way as in ml)
(f) avoid making text too small or too big. too small (less than 10) and people can't read it. too big (>14) and people struggle to skim over it (their eyes have to move too much)
......I don’t use colour (as it costs 7p/sheet verses B/W at 1p/sheet).....
Not necessarily..
Don't compromise on how good a sheet can be made to look on monitor. To print black and white, simply configure in page setup to print in black and white.
Like This post !!
I m always using ALT + EST, not verymuch confirtable with cell style. will try to use color schemes (new feature)
Regards
!$T!
Hi Stephen,
Do you have some non-proprietary samples you may share on drop box or Windows Live SkyDrive?
Thanks
w
Great post!
Which key ist EST from the shortcut "ALT+EST".
I am using a german keyboard layout and have never heard something about an EST key.
Thanks
Carsten
Hi Carsten...
If you are using English version of Excel, then press ALT+E then leave the alt key, E key and then press S, then press T
For German version of Excel, the keys would be different. I am not sure what they are.
it was nice MS come up with all the color schemes. However, corporate culture (or your boss) sometimes dominate or predetermine what style a spreadsheet should look like. So I hardly get a chance to use #1 to #3 shown above.
Most of the times, it is someone else who wants a certain report or analysis gets to decide how s/he wants it to look like. I see myself more like a line chef or engineer. Others get to be the architect and I'm just a builder transforming a design into a real home. I don't get much say in it unless they are asking me to build a multistoried building on a single tooth pick as foundation.
Hi Chandoo,
thank you for your reply. Now I understand. It's something like searching for the ANY Key, because some program is displaying "Press any key to continue..."
But to find the german version of this shortcut:
ALT+E calls the Edit-menue? And for what are the S and T. Just tell me the english names of the menueitems, please.
I think then I will find it.
Carsten
@Carsten
Alt+EST is
(E)dit;
paste (S)pecial;
forma(T)s
Excellent post guys!
@Carsten,
Try to know how to find the shortcuts in the excel menu bar itself.
You click Alt + any of the underline character in the menu bar, then excel will take you to that particular menu field.
Now you can find different options in the dropdown menu. And each option has the name. Each name has underline in any of the characeter. That underline character is nothing but the shortcut key to execute that option.
Like this you can find in excel all the options and their shortcut keys.
Coming to the above example..
Once you click alt + E, it will take you to the "EDIT" drop down menu. Under Edit there are so many options like cuT, Copy, Paste, paste Special, fIll.... etc., I think you can find underline under 't' in cut..'p' in paste..'s' in paste Special. You need to click the underlined character for the required options...Here the 'S' underlines for Paste Special option...
Once you click 'S' it will open paste special options box...again you will find the same underlines in each of the names...here you can find different opetions like All, Formulas, Values, formaTs...etc. 'v' is nothing but Values option. Once you click V in the key board..it will execute paste special values option.
As Summary Alt + (E)dit + paste (S)pecial + (V)alues
Now you can find the shortcuts your own. all the best.
Regards,
Saran
lostinexcel.blogspot.com
You can also customize the quick access toolbar.. Once you find the icon you regularly use, right click and then select Add to quick access toolbar and once you are done, when you press Alt key it will be highlighted 1,2,3,4 etc depending upon the sequence of the icon..
Ctrl-ES is sooooo 2003.
Ctrl+Alt+V all the way baby!!!
You can DOUBLE-CLICK Format painter button to copy the formatting multiple times. Once you are done, press ESC key.
//
Jinesh,
This is a great tip that I use multiple times daily. People are always in awe when they see this one!
Jesse
Hi,
How to apply the custom styles for cells from the sql table, by using c# program.
Thanks & Regards,
Satheesh
[…] You can use the Page Layout section in Excel to apply colour themes to your reports. Chandoo.org has some useful Excel tips. […]
[…] http://chandoo.org/wp/2011/12/05/excel-formatting-tips/ […]
Hi i want to print a page which have bottom line to print on each page end how to do that pls explain
Thanks Sir
Thanks alot
Very useful thanks
thank you too much
your tips are awesome.
How to show a table with around 20-25 columns in the dashboard in the first page itself? I mean, within the dashboard area.
Is there anyway we can add a horizontal scroll bar for the table?
@Kiran
You never add tables directly to a dashboard
You add cells that reference a table
By reference I mean it gives you the ability via Formula or VBA to scroll up/down, Left/right or re-order the data
Think of it as a window into the table
This is discussed regularly in Chandoo's dashboard samples
Have a look at the 2 links in Item 1: http://chandoo.org/wp/welcome/
I'd then suggest asking a specific question in the Chandoo.org Forums and attach a sample file for a specific answer.
love it!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I have a table of value for a month, with no data for few dates.
I created a chart basing on above data.
In the chart I find calendar dates, even though few dates with no data are not available in the table.
How to remove the dates in the chart for those without data?