How to Visualize Your Data Analysis with Excel Power Tools

Excel has an excellent range of power tools that you should use to analyze your business data. Whether you need to export for business intelligence, pivot your data to find trends, or simply use your data more visually, Excel has you covered with Excel vs. Google Sheets:Which is better for you? Excel vs. Google Sheets:Which is better for you? Do you really need Excel? Both desktop and online solutions have their advantages. If you're having trouble choosing between Excel and Google Sheets to manage your spreadsheets, let us help you decide. Read more.

Today we're going to explore the realms of Power Pivot, Power Query, Power View, Power Map, and if we have some time left, cloud-based Power BI, the Excel Business Intelligence tool. It sounds like a lot, but we can cover all these topics and We leave you eager to learn more.

One of Excel's biggest selling points is these tools and their interrelated nature:once you use one tool for analysis, it's easy to see where the others could provide more support. With extensive knowledge of these Excel tools, you will be the master of your data.

Power Pivot

Power Pivot is one of the power tool add-ins for Excel. Power Up Excel with 10 add-ins to process, analyze and visualize data like a Pro Power Up Excel with 10 add-ins to process, analyze and visualize data like a Pro Vanilla Excel. Amazing, but you can make it even more powerful with plugins. Whatever information you need to process, chances are someone has created an Excel application for it. Here is a selection. Read more . In his 2010 release, Bill Jelen, founder of Mr. Excel, called Power Pivot "The best new feature to move to Excel in twenty years," so you start to understand the gravitas of the Excel extension.

Office 2016 saw Power Pivot included Upgrade to Office 2016 for free today with your Office 365 subscription Upgrade to Office 2016 today for free with your Office 365 subscription Microsoft Office 2016 for Windows is here and brings lots of smart new features. If you have an Office 365 subscription, you can get it now for free, and we'll show you how below. Read More of Excel. 2013 repertoire. It is frequently used for big data analysis. Follow our step-by-step demo to learn all about it. Read more and data modeling tool. Power Pivot can handle large volumes of data imported from a variety of external sources, extending the functionality of the standard pivot table feature. Use Power Pivot too:

• Import and manage millions of rows of data from multiple sources.
• Build analytical relationships between data from different sources, quickly creating and modeling data tables using standardized functions Mini Excel Tutorial:Using advanced counting and aggregating functions in Excel Mini Excel Tutorial:Using advanced counting and aggregating functions in Excel to formulas more advanced Excel. But they can help you save a lot of time when you need to gather information about the data in your spreadsheet. Read more.
• Incorporate data into other Excel power tools:pivots, charts, grids, energy view, energy map, and more.
• Use Data Analysis Expressions, or DAX, a new formula language that extends Excel's data manipulation facilities. You can read an introduction to DAX here.

Power Pivot itself is an important tool for Power BI. The enhanced data tables and models you can build using Power Pivot, along with seamless integration with Power View and Power Map, give you the opportunity to uncover hidden trends, develop business insight, and most importantly, turn that wonderful information raw into something analysable..

Power Pivot Charge

Power Pivot first appeared as an add-in, but is now included in the standard installation of Excel. You just have to enable it..

Office 2013/2016 :Open Excel and go to File> Options> Add-ins . In the Manage box, at the bottom of the screen select COM Add-ins , followed by Go . Make sure that Microsoft Office Power Pivot for Office 20xx is selected, and press OK . It should now show up with your existing tabs.

Office 2010: download Power Pivot for Microsoft Excel 2010 using this link [No Longer Available]. Run the installation package. Once installed, the Power Pivot tab will appear in Excel and if you go to File> Options> Add-ins you should spot PowerPivot for Excel as a COM plugin.

Power query

Power Query is another powerful data analysis tool that can be used to extract data from a wide range of data sources, cleanse and transform that data, and deliver that data in an accessible format. Power Query is important for regular and professional users. Regular users can exercise more control over columns, formulas Need help with Excel formulas? 7 resources to check out Need help with Excel formulas? 7 Resources to Reference Excel is the gold standard of spreadsheets. If you must use Excel and yet become familiar with it, these resources will quickly introduce you to the basics and beyond. Read More With Power Query, you can:

• Find and connect data from a wide range of sources, merge and shape that data to match your own requirements, or use built-in modeling tools, Power Pivot and Power View, to drive your data.
• Use the built-in JSON parser What is JSON? Description from a layman What is JSON? A Layman's Overview Whether you plan to be a web developer or not, it's a good idea to at least know what JSON is, why it's important, and why it's used all over the web. Read more to create big data visualizations about your big data analysis How to become a data scientist How to become a data scientist Data science has gone from being a term newly coined in 2007 to being one of the most sought after disciplines nowadays. But what does a data scientist do? And how can you enter the field? Read more.
• Share and manage your queries through your sources, as well as by exporting them to Power BI or your business.
• Search public data sources online, including Wikipedia, Azure, and Data.gov.
• Un-pivot your pivot tables.

The range of Power Query data sources is extensive:

• web page
• Excel or CSV file
• XML file
• Text file
• Folder
• SQL Server Database
• Microsoft Azure SQL Database
• Database Access 5 Easy-to-Use Free Alternatives to Microsoft Access 5 Easy-to-Use Free Alternatives to Microsoft Access Microsoft Access is a complex database tool with a steep learning curve. If you're looking for more intuitive and free alternatives with similar features, we've got you covered! Read more
• Oracle Database
• IBM DB2 database
• MySQL Database
• PostgreSQL database
• Sybase Database
• Teradata Database
• SharePoint List
• OData Feed
• Microsoft Azure Marketplace
• Hadoop File (HDFS)
• Microsoft Azure HDInsight
• Microsoft Azure Table Storage
• Active Directory
• Microsoft Exchange
• Facebook

As with Power Pivot, Power Query also comes as a core tool for Power BI, as well as providing visualization tables for Power Map and Power View. As mentioned above, Power Query can also be used to un-pivot tables, restoring them to their original state for further analysis.

Loading Power Query

Power Query also appeared as an add-in, and as of Office 2016 it is included as a default feature. Again, just enable it.

Office 2016: open Excel and go to File> Options> Add-ins . In the Manage box, at the bottom of the screen select COM Add-ins , followed by Go . Make sure that Microsoft Office Power Query for Office 2016 is selected, and press OK . Power Query tools are hosted under the Data tab.

Office 2010/2013: Download Microsoft Power Query for Excel. Locate your download and install. Once installed, you should see the Power Query tab. If not, go to File> Options> Plugins . In the Manage box, at the bottom of the screen select COM Add-ins , followed by Go . Make sure Microsoft PowerQuery for Excel is selected, and press OK .

Power View

Next:PowerView! Power View is primarily an interactive visualization tool used to provide a drag and drop interface for rapid model building. Many Excel experts use Power View as their primary Power Pivot visualization tool. 4 data visualization tools to captivate data journalism. 4 data visualization tools to captivate data journalism. Words and numbers are not enough. In the end, the visual will capture and convince your audience. Here are the tools to help you turn your data into something big. Read More

• Power View groups the data by corresponding values. For example, if we're mapping shapes related to Tucson, AZ, but our data returns values ​​for Dove Mountain, Rancho Vistoso, and Barrio Historico, Power View intelligently groups them together and returns a single value.
• Power View can connect to a number of different data models in a workbook, which means it can show clients a range of visualizations and data tables, rather than the only one available to base-Excel.
• Modify internal data models without leaving the Power View sheet:update your visualizations on the fly, create new relationships between current data, and introduce key performance indicators (KPIs) based on those relationships.
• Advanced Pie Charts 8 Types of Excel Charts and Graphs and When to Use Them 8 Types of Excel Charts and Graphs and When to Use Them Charts are easier to understand than text and numbers. Charts are a great way to visualize numbers. We show you how to create charts in Microsoft Excel and when to best use which type. Read More

Loading PowerView

Unfortunately, Power View is only available for Excel 2013 and 2016, although it is compatible with SharePoint 2010, which means you can base a Power View file on SharePoint 2010 with the SQL Server 2010 Reporting Services Add-in. However, features like hierarchies and KPIs may stop working.

Office 2013/2016: open Excel and go to File> Options> Add-ins . In the Manage box, at the bottom of the screen select COM Add-ins , followed by Go . Make sure Power view is selected, and press OK .

Power Map

Power Map allows you to plot and visualize data in three dimensions. It is particularly well-suited for visualizing geographic or temporal data, and certainly provides visual relief to clients or businesses looking to better understand their data, bringing together insights not seen in the traditional, two-dimensional workbook.

Power Map can:

• Plot millions of rows of data on a three-dimensional map, using data taken directly from a table or using a data model.
• Capture screenshots and take cinematic walkthroughs through your data, refresh the data to see how the time-stamped information changes.
• Filter extensively on slices of data to compare how different factors affect your data.
• Build custom regions to illustrate localized data models.

Loading power map

If you have an Office 365 ProPlus or Office 2016 subscription Power Map will already be installed in Excel as part of the self-service business management tools. Activate it by going to File> Options> Plugins . In the Manage box, at the bottom of the screen select COM Add-ins , followed by Go . Make sure Microsoft Power Map for Excel is selected, and press OK .

Office 2013: Download Excel Power Map Preview and follow the installation guide. Once installed, follow the procedure above to activate.

To use Power Map, open Excel and go to Insert> Map .

Power Map is not available for Excel 2010.

BI Power

The tools mentioned above are amalgamated as cloud providers. Power second usiness I Intelligence tool. It comes in two types:Excel-based Power BI or Power BI for Desktop. There are advantages to both, but many companies use both in hybrids.

It makes sense:Excel-based Power BI is extremely well integrated into Excel and has a large following, while Power BI for desktop offers a wide range of visualizations for publishing interactive dashboards. Visualize your data and make your spreadsheets easy to use with an Excel dashboard. Visualize your data and make your spreadsheets easy to use with an Excel Dashboard Sometimes a simple spreadsheet format isn't attractive enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More Ambos clientes utilizan el mismo motor, el mismo idioma, el mismo alojamiento en la nube y el acceso a SharePoint, por lo que no hay una transición desordenada entre los clientes o los usuarios..

Office 2016 produjo una serie de actualizaciones de Power BI, que incluyen:

• Alrededor de 30 nuevas funciones y expresiones DAX..
• Editor de consultas para facilitar la configuración de datos, agregando más profundidad analítica a Power Query.
• Power BI sugerirá relaciones entre los modelos de datos y los campos de tablas dinámicas, así como la adición de la agrupación de tiempos para las tablas dinámicas..
• Amplia gama de herramientas de pronóstico para el modelado de datos..
• Resúmenes de campo con operadores adicionales..
• Conectores HDInsight Spark y Azure SQL Data Warehouse.

El lema de Power BI es “Permítanos ayudarlo a dar vida a sus datos.,” poniendo énfasis en dejar que sus datos hagan el trabajo por usted, explorando sus datos usando “lienzo de arrastrar y soltar de forma libre” sistemas, y trayendo su “cuentos” a la vida. yo personalmente odio el uso de la palabra “cuentos” en este contexto, pero todo es brillante, práctico y relativamente abierto a los recién llegados.

En última instancia, puede hacer uso de cada componente individual de la pila Power BI para potenciar sus datos en una ventaja comercial competitiva. Power BI se convierte en el spool central, un hilo de dibujo de cada servicio para expandir las capacidades de su conjunto de herramientas de análisis a través de paneles, informes fácilmente publicados y modelos de datos visualmente atractivos..

Energía de carga BI

Para descargar Power BI para escritorio independiente, siga este enlace, luego siga las instrucciones de instalación o diríjase al sitio dedicado de Power BI aquí..

El sitio dedicado le ofrece la oportunidad de registrarse como usuario profesional por $ 10 por usuario al mes. Para esto, su capacidad de datos se eleva de 1 GB por usuario por mes a 10 GB, su tasa de actualización de datos se eleva a un horario por hora y puede procesar más de 1 millón de filas por hora. Además, obtiene acceso a los Grupos de Office 365 para Power BI, lo que facilita el intercambio entre equipos. Mire el sitio para saber más sobre lo que Power BI podría hacer por usted..

Oficina 2016 :Me temo que tendrás que aguantar un poco más. Power BI solo se puede usar con las versiones ProPlus de Office y Microsoft confirmó recientemente que esto no se lanzará hasta principios de 2016. Hay detalles para administradores aquí..

Oficina 2013: Solo Office 2013 ProPlus, o aquellos con una suscripción de Office 365 ProPlus, pueden hacer un uso completo de Power BI, utilizando las herramientas existentes que describimos en la sección anterior..

Oficina 2010: Lo sentimos, amigo, no tiene la versión requerida de Excel para utilizar todas las características más recientes de Power BI. Sin embargo, Power BI funciona con cualquier libro de trabajo de XLSX Excel, por lo que siempre puede migrar algunos de sus datos a la versión de escritorio independiente.

Resumen de herramientas eléctricas

Hemos cubierto el núcleo de poder de Excel y le presentamos la herramienta de inteligencia empresarial Power BI. Con suerte, te sentirás cómodo activándolos en tu propio sistema y teniendo un buen revuelo con sus muchas características..

Vale la pena recordar que cada una de las herramientas independientes se integra en Power BI, y con las últimas actualizaciones de Power BI para escritorio, se pone aún más énfasis en Power Query y la amplia gama de herramientas de Power View. Lo que significa que puede explorar y ampliar su propio entendimiento de cada herramienta con el sólido conocimiento que se puede implementar en un poderoso golpe de inteligencia empresarial.

¿Cuál es tu herramienta eléctrica de Excel favorita? ¿Usas Power BI de escritorio o integrado? Háganos saber a continuación.