Work Smart by Microsoft IT
SelfSer!ice "I in Excel #$%& Selfser!ice business intelligence '"I( features in Microsoft Excel #$%& spreadsheet software make disco!ering and !isualizing data easy) easy) These tools* which include +ower ,uery* +ower +i!ot* +ower View* View* and +ower Map* work seamlessly together to extend "I functionality and help you turn data into meaningful information) This document co!ers Excel "I capabilities 'in Microsoft -.ce &/0 +ro+lus and -.ce #$%& +rofessional +lus( apart from Microsoft +ower "I for -.ce &/0) 1owe!er* the two "I options complement each other2 3fter 3fter creating compelling content and reports in Excel* you can use +ower "I for -.ce &/0 to amplify those e4orts with interacti!e sharing* collaboration* and data management)
Topics T opics in this guide include2 into Excel
Sharing workbooks
Merging Merging importe imported d data Visualiz Visualizing ing your your data
# 5 Selfser!ice "I in Excel #$%&
+utting Excel "I to work Excel #$%& "I capabilities will be useful for anyone who wants to2 •
3nalyze data of wideranging size6from a few rows to more than %$$ hundred million)%
•
Speed up analysis of* easily clean up* and shape data)
•
Easily na!igate multidimensional and tabular data models)
•
3ccess* mashup 'combine(* and analyze data from !irtually any source)
•
+ro!ide compelling data !isualization and presentation experiences)
+ut Excel "I capabilities to work and disco!er* analyze* and !isualize data to gain !aluable business insights) The +ower ,uery* +ower View* +ower +i!ot* and +ower Map tools are ideal for this purpose)
Importing data into Excel +ower ,uery is one of three ways to bring data into Excel from disparate large data sources) It complements the rich nati!e import capabilities of Excel 'features on the Excel 7ata ribbon* for example( and of +ower +i!ot) With its design for a consistent user experience* +ower ,uery is a great way to get started with selfser!ice disco!ery* transformation* 8ltering* shaping* and mashup of data)
7e8ning 9ueries with +ower ,uery +ower ,uery is not included in Microsoft Excel) It is a!ailable as a free addin) To install +ower ,uery2 %) -n the +ower ,uery page in the Microsoft 7ownload :enter* select the &#bit or /;bit architecture* run the Microsoft Windows Installer* and follow the onscreen instructions)
#)
In the Get External Data group on the POWER QUERY tab* choose a data source)
% /;bit !ersion
& 5 Selfser!ice "I in Excel #$%& L o a d
O n l i +ower ,uery can import data from more than #$ types of external sources) n e t h e s e a t r a c b h l e 3s an example* Online Search is great r for getting external data sets '+ublic 7ata :atalog() If you ha!e +ower "I* you can also search the Enterprise 7ata e f :atalog6data shared within your organization) s r u o l m t s t h e s o u r c e a s i s , o r
i n c l u d e a p r e v i e w o f
e d bottom of the data window* click EDIT to open the Power Query Editor. # 3t the t i h your data and shape or transform it With the t +ower ,uery Editor* you can !iew before loading it into a spreadsheet) =ou ecan2 a
•
•
d :ombine data from multiple data sources) r
•
>ilter t and group rows in a table)
i
s t
r a f n 3ggregate data from a column) e s w Merge* remo!e* and unpi!ot columns) f o
Expand a column containing an associated table)
•
•
•
t f
o f
; 5 Selfser!ice "I in Excel #$%&
later)
The APPLIED STEPS
To % &) ;)
box tracks the steps you perform asgear you The transform icon and signifies shape that you your candata. change the settings associate You can d with a load your step, such query as filter results complete the 9uery2 criteria. into a worksheet @nder LOD SETTI!GS* check Load to wor"#heet or Load to Data $odel ) or the :lick the %O$E tab* and then click xcel &&ly ' (lo#e ) !ata
Merging imported data
There are two di4erent approaches to merge data from di4erent sources) =ou can create separate tables in the 7ata Model and use relationships to logically merge them* or you can use $er)e Querie# in +ower ,uery) To continue the example abo!e* here is how you merge the data with +ower ,uery) To merge the data from the !arious 9ueried sources2 %
-n the %O$E tab* click $er)e Querie#)
0) In the $er)e pane* select tables and matching columns to create a merged table* and then click O*. In this example* two data sources contain information listed by state)
0 5 Selfser!ice "I in Excel #$%&
Excel displays the 8rst sheet with a new column on its right side) /) :lick the doublearrow
icon next to !ew(olu+n)
Excel displays a dialog box from which you can choose columns that you want to add to a merged 9uery sheet)
=ou can repeat these steps to merge as many 9ueries as you need)
/ 5 Selfser!ice "I in Excel #$%&
The +ower +i!ot addin +ower +i!ot plays an important role in merging and importing data) +ower ,uery supports merging by more than one column* while +ower +i!ot supports only a single column) +ower ,uery also allows duplicates in keys and can create duplicate rows* but doesnAt support importing from online analytical processing '-B3+( or tabular dataC only +ower +i!ot supports that) Dati!e Excel capabilities and +ower +i!ot are much faster at importing compared to +ower ,uery) 3s you learn more about "I* youAll want to explore +ower +i!ot) In Excel #$%$* you can install the +ower +i!ot addin to import and relate large amounts of data from multiple sources) In Excel #$%&* much of this functionality 6including the infrastructure that supports it6is built directly into the 7ata Model) This means that e!en without the addin* you can import millions of rows from multiple data sources and manage data connections)# =ou can also create relationships between tables in the 7ata Model) The +ower +i!ot addin for Excel pro!ides more ad!anced modeling capabilities) >or example* you can2 •
•
:reate relationships using the draganddrop feature in 7iagram View)
•
3pply formatting for +ower View and +i!otTable reports)
•
•
7e8ne hierarchies to use throughout a workbook* de8ne key performance indicators '+Is( to use in +i!otTables* and de8ne perspecti!es) @se the 7ata 3nalysis Expressions '73F( language to create calculated columns or explicit calculated measures)
Measures are perhaps the most popular feature that +ower +i!ot supports) >or more information* see Measures in +ower +i!ot for Excel #$%& on microsoft)com) 3dditionally* the entire 7ata Model in +ower +i!ot6including tables* columns* calculations* hierarchies* and customizations6is exposed as reportready elements in +ower View) The +ower +i!ot addin is built into Excel #$%& but is not enabled by default) To enable it2 %
-n the ,ILE tab* click O&tion#* and then click dd-In#)
G) In the $ana)e box* click (O$ dd-In# * and then click Go) H) :heck the $icro#ot O/ce Power Pi0ot in $icro#ot Excel 1234 box* and then click O* )
# /;bit !ersion
G 5 Selfser!ice "I in Excel #$%&
Visualizing your data Two key tools for !isualizing "I data are +ower View and +ower Map) @se the Power 5iew button on the I!SERT tab to compile data and charts in a single !iew to bring your data to life) 7isco!er insights about your data by using highly interacti!e and powerful data exploration* !isualization* and presentation features that are easy to apply)
@sing +ower View The +ower View addin is included with Excel but not enabled by default) The 8rst time you click the Power 5iew button on the I!SERT tab* Excel will remind you to enable it) To create simple !isualizations2 %
-n the I!SERT tab* click Power 5iew)
) In the Power 5iew ,ield# pane* click the 8elds that you want to include in your !isualizations or drag them to the report can!as) %$) Select your !isualization in the Switch 5i#uali6ation# group on the DESIG! tab)
H 5 Selfser!ice "I in Excel #$%&
:harts and 7rilling 7own =ou can make charts that drill down when you doubleclick the !isual data element of that chart6a column in a bar chart or a slice in a pie chart* for example) =ou can also cross8lter one chart with another) >or example* if you click a bar in a bar chart* the part of the pie chart that applies to that bar will be highlighted* while the rest of the pie is shaded)
5 Selfser!ice "I in Excel #$%&
Suppose you ha!e created pie charts and a bar chart that are connected to the same data set) To drilldown2 %
In the Power 5iew ,ield# pane* drag category 8elds to Tile 7y* (olor* Si6e* Slice#* or other boxes* to de8ne the content and appearance of your pie chart)
%%) 7oubleclick one of the pie colors) %#) The colors of the pie chart now show the percentages of the 8elds* 8ltered for the pie color that you doubleclicked) In this example* corresponding bars in the bar chart will be highlighted 'only one in this case() %&) To go back up* click the arrow in the upper corner of the pie chart* next to the 8lter icon) %;) :lick the drillup arrow in +ower View chart !isualizations)
>or more information about creating pie charts* including a !ideo* see +ie :harts in +ower View)
+ower Map +ower Map is a geocentric and temporal mapping feature of Excel that brings location data into engaging threedimensional map !isualizations) With +ower Map* you can create locationbased reports* !isualized o!er a time continuum* that !isually tour and o4er insight into data) To install +ower Map2 %
-n the +ower Map page in the Microsoft 7ownload :enter* click Download to run the Windows
%$ 5 Selfser!ice "I in Excel #$%&
Installer* and follow the onscreen instructions) %0)
%/) In Excel* open a workbook that has the table or 7ata Model data that you want to explore in +ower Map) >or information about using your own data* see Jet and prep your data for +ower Map) %G) -n the I!SERT tab* click $a&) :licking $a& for the 8rst time automatically enables +ower Map) %H) In the Launch Power $a& dialog box* click !ew Tour ) +ower Map uses "ing to geocode your data based on its geographic properties) 3 globe will appear) In the Layer 3 pane* your 8elds should already be selected in the (hoo#e Geo)ra&hy and Geo)ra&hy and $a& Le0el boxes* if your data set contains that information) %) :lick !ext to start adding layers and aggregating and further !isualizing your data on the map) The subse9uent steps depend on your data set) 1ere is an example of a +ower Map !isualization) It combines geolocation 'towns near 7allas* Texas(* the decade in which homes were built* their s9uare footage* and kilowatt hours 'kWh( of the electricity that they consume o!er time) >or more information* including an animated presentation of the exampleAs !isualization* see Jet started with +ower Map)
%% 5 Selfser!ice "I in Excel #$%&
Sharing workbooks Many of the "I capabilities that are a!ailable in Excel are supported in Excel Ser!ices in Microsoft Share+oint Ser!er #$%& 'onpremises() This means that you can !iew and interact with workbooks in a browser window much as you would by using the Excel client) The best way to share workbooks and reports is to take ad!antage of Microsoft +ower "I for -.ce &/0* which pro!ides an organizationwide selfser!ice "I infrastructure* and brings Excel workbook sharing* online collaboration* and IT infrastructure together into a holistic o4ering) 3dditionally* the Microsoft +ower "I sites application transforms a Microsoft Share+oint -nline site into a robust* dynamic location to share and 8nd Excel workbooks and pro!ides a !isual* interacti!e !iew tailored to "I) It also gi!es you easy access to other "I features a!ailable in +ower "I for -.ce &/0* such as the Manage 7ata +ortal* the +ower "I for -.ce &/0 3dmin :enter* and +ower "I ,K3 to use natural language to 8nd* understand* and report on your data)
>or more information WhatLs new in Excel #$%& http2o.ce)microsoft)comenusexcelhelpwhatsnewinexcel #$%&13%$#H$&$H)aspxN:TTO%
%# 5 Selfser!ice "I in Excel #$%&
Excel #$%& ,uick Start Juide http2o.ce)microsoft)comenussupportexcel#$%&9uickstart guide13%$&/G&/$)aspxN:TTO% Version compatibility between +ower +i!ot 7ata Models in Excel #$%$ and Excel #$%& http2o.ce)microsoft)comenusexcelhelp!ersioncompatibility betweenpowerpi!otdatamodelsinexcel#$%$andexcel#$%& 13%$&#;#/)aspxN:TTO% Make the switch to Excel #$%& http2o.ce)microsoft)comenusexcelhelpmaketheswitchto excel#$%&
3, http2o.ce)microsoft)comenusexcelhelppowerbiforo.ce &/0fa913%$;#%G&)aspx @se a shared workbook to collaborate http2o.ce)microsoft)comenusexcelhelpuseashared workbooktocollaborate13%$#G;HGG)aspx Work Smart by Microsoft IT http2aka)mscustomerworksmart Modern IT Experience featuring IT Showcase http2microsoft)commicrosoftIT
This guide is for informational purposes only. MICROSOFT MAKES NO ARRANTIES! E"#RESS! IM#$IE%! OR STAT&TOR'! AS TO T(E INFORMATION IN T(IS %OC&MENT. ) *+,- Mirosoft Corporation. All rights reser/ed.
