Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
What is Power View for Excel?
Excel Power View is a data visualization technology that helps you to create interactive visuals like graphs, chart, etc. It helps you to analyze data by looking at the visuals you created. Power view can give your excel data a new life and make it more meaningful. This will help you the get insight from data so that you can make a decision based on the data.
How to Enable Power View for Excel?
If you want to use Power view than you need to enable add-in named Power View in Excel. After the add-in is enabled, it will show under the Insert tab in the Menu bar.
Follow the below steps:
Click on File ->Option
Excel Options screen will pop up. Select Add-ins and you will see Manage dropdown at the bottom of the pop-up. See highlighted below:
Select COM Add-ins from the dropdown then click on GO button.
COM Add-ins screen will pop up. Check on Microsoft Power View for Excel and Click OK.
This will add Power View under Insert Tab.
How to Enable Power View in Excel 2016?
Remember that Power View is only in Excel 2013 and higher versions. If you are using Excel 2016 then you may find Power View is missing even after the add-in is enabled. You need to follow below steps to enable Power view.
Step 1: Click on File -> Option
Step 2: Excel Options screen will pop up select Customize Ribbon.
Step 3: Under Customize the Main Tab, Extend Insert option than click on New Group.
Step 4: Now go to Choose command from shown in the left and select All Commands from the dropdown now select insert a Power View Report. Click on Add and this will add Power View under your Insert Tab. (When you click on add make sure New group (Custom) is selected else an error will pop up). Select Ok.
Now you can see the Power View option under Insert Tab in New Group section:
How to Use Power View in Excel?
Once Power View is enabled, you are all set to use it. Follow the below steps to create visuals using power view.
I have some sales data for a superstore and if someone asks me to create an interactive dashboard.
Now Organize data in form of a table.
To create a table, follow the below steps.
Step 1: Click anywhere within the data. Go to Menu Bar – Insert – Table
Step 2: When you click on Table, it will automatically select the data range. However, if you need to change the data range you can put the range under “Where is the data for your table?”. Check “My table has headers” if your data has headers. Note: You can use ctrl + t shortcut to create the table.
It will look like below.
Now your data is ready for creating the power view in excel.
Click Insert – Select Power view under Insert Tab
This will open a new sheet within the workbook (This may take some time to create Power View sheet. Please wait)
Once the window is opened you will see a table on the left side, filters in middle and power view fields on the right side.
You can give any name to the Power View dashboard. You can review the field and select or deselect the field according to the requirement.
You can see a sign ∑ is used before some fields that means this fields contains a value which should be used to do the calculation.
Power View Dashboard
Now Let’s start creating a dashboard:
Select Category and Sales value from Power View fields.
Click on Insert – Column chart. This will look like below.
Click on the dashboard again and now select State and sales amount and then select Bar chart. When you hover on the chart, this will show sort by option. You can sort it accordingly.
Now the chart will look like below.
Let us see sales based on segments.
Select Segment and Sales from the field list and then choose Pie chart under ‘Other chart’ option. The final chart will look like below.
Let us see what insight can be found from this Dashboard.
- Highest sales received from Category “Technology”
- Top 3 state where sales were done are California, New York, and Texas. Similarly, you can tell Top 5 or Top 10 or whatever is required based on the data
- Consumer Segments contributing the most with more than half of total sales, Corporate stands at 2nd and Home Office at 3rd.
We can create more dashboard using different Charts and Maps under design Tab when you select data in the dashboard.
Things to Remember
- Data should be arranged properly in rows or columns and no Column should left blank between the data.
- You need to install Microsoft Silverlight before using Power View if that was not installed previously.
- Restart the application after you install Power View Add-in and Silverlight else this will through error.
This has been a guide to Power View in Excel. Here we discuss how to enable power view in excel and create a dashboard for the same with the help of an example. You can learn more about excel from the following articles –