Find out how the Excel "Power" Tools - Power Pivot, Power Query and Power View - can change your life!
Learn more about Excel Pivot Tables and how they can make you 80% more productive in 20% of the time.
Many people worry that creating a PivotTable is time consuming, but if you have a well-organised data source, you can create a PivotTable in a snap.
Tables and PivotTables are a great match! When you use a table as source data for a PivotTable, Excel will automatically expand and shrink the table as you add or remove data, so your PivotTable will always stay in sync with your data.
You might think you have to be working with numbers to use a PivotTable but, by default, a PivotTable will count any text field.
When you've created a PivotTable from data in the same worksheet, you can remove the data if you like and the PivotTable will continue to function. Each PivotTable has a pivot cache that contains an exact duplicate of the data used to create the PivotTable.
Although PivotTables can automatically group data in many ways, you can also group items manually.
One of the most powerful features of PivotTables is the ability to group data by numbers. You can use this feature to group by age range, price range, or any numerical range that makes sense in your data.
When you add fields to a PivotTables, the PivotTable will display the name that appears in the source data. Value field names appear with "Sum of" or "Count of" at the start.
It may seem counterintuative, but you can add the same field to a PivotTable more than once. In fact, there are situations where you'll want to do just that.
Anytime you add a value field to a PivotTable, make sure you set the number format on the field itself.
Anytime you see a subtotal or grand total in a PivotTable, you can easily get to the exact data that makes up that value using the "drill down" feature built in to any PivotTable.
There is no shortage of data available today, with huge amounts of it being constantly generated by ERP systems, POS systems, PLC systems and Biometric systems, to name but a few.
Companies are needing to find ways to effectively analyse the data, to leverage the information and make informed decisions, however many of the options available are very expensive and require a lengthy period of implementation, plus human natures makes most staff resistant to learning new systems so all in all, decision makers are faced with a challenge on many levels.
Excel has become the main tool for manipulating and analyzing data, and developing reports in business today, and with the release of Excel 2013, Microsoft introduced even more powerful business intelligence tools.
To understand how the Excel Business Intelligence tools work together in Excel 2013, you need to understand the function of each tool and how it fits into the “traditional” Excel model.
Extract, Transform, Load
Power Query is an intuitive, easy to use tool for importing, transforming and working with data.
Spreadsheets are the basic feature of Excel. Launched in 1985, there are now over 700 million Excel users worldwide.
Tables were introduced in Excel 2007, they allow you to do quick sorting and analysis of your data.
Power Pivot was introduced as a free add-in for Excel 2010, it gives you the ability to access, process and handle well over a million rows of data, more than a standard spreadsheet can process.
Pivot Tables are an easy and convenient way to build intelligent, flexible summary tables, allowing you to quickly derive insight from your business data.
Pivot Charts enable you to visualize a PivotTable. You can quickly change a portion of data displayed, making PivotChart ideal for presentation of data in reports.
Power View is an interactive data exploration, visualisation and presentation tool that gives you strong insight in to your business .
So what is the bottom line?
Before spending time and money on new data analysis tools, find out exactly how learning how to use all the Excel Business Intelligence Tools effectively will allow you to quickly and easily analyse your data and gain insight into it without any major resource investments.
Over a billion people around the world use Excel today, far more users than any other BI vendor can claim.
Original article from Thor Olavsrud (IT Security, Open Source, Microsoft Tools and Servers for CIO.com). Follow Thor on Twitter @ThorOlavsrud.
Companies that rate themselves substantially ahead of their peers in their use of data are three times more likely to rate themselves as substantially ahead in financial performance, according to findings from the Economist Intelligence Unit.
Working with and making business decisions based on data is good for your company's bottom line. Companies that have embraced a data-driven culture—rating themselves substantially ahead of their peers in their use of data—are three times more likely to rate themselves as substantially ahead of their peers in financial performance, according to findings by the Economist Intelligence Unit in a survey sponsored by Tableau Software.
In October 2012, the Economist Intelligence Unit surveyed 530 senior executives from North America, Asia Pacific, Western Europe and Latin America across a broad range of industries. The survey found that the most successful companies have adopted a data-driven culture in which they maximize the use of data by providing necessary training and promoting the sharing of data across all levels of employees and departments.
"The importance of data-driven thinking is not new," says Jim Giles, author of the Economist Intelligence Unit report, Fostering a Data-Driven Culture. Many executives are familiar with the concept. The rise of data-driven companies, from Facebook to Walmart, shows how powerful the approach can be. But what does it mean in practice? And what are the benefits of adopting a data-driven culture within an organization?
Data-Driven Culture Is About More than Data Specialists
"Let us start with what a data-driven culture is not," Giles says "It is not a belief that data are an issue for someone else in the company, a job for a data specialist or perhaps the IT department. There is still a perception that a data specialist, perhaps a recent statistics graduate, should be parachuted into an organization to advise on how to work magic with data, much as a computer security expert would be called on to help shore up a company's IT networks."
This, Giles says, is flawed thinking. Instead, he says, forward-looking organizations don't concentrate data in the hands of an individual or small group but integrate data into their day-to-day operations.
"They are placing data at the heart of almost all important decisions," he says. "And they are tolerant of questioning—even dissent—about business decisions being made, as long as the questioning is based on data and their analysis. This is what it means to adopt a data-driven culture."
Top-Performing Companies Have Adopted a Data-Driven Culture
And the adoption of data-driven culture is bearing fruit for many organizations. The Economist Intelligence Unit found that only 11 percent of respondents felt their organizations make substantially better use of data than their peers. But more than one-third of that group was comprised of top-performing companies. On the flip side, of the 17 percent of executives that said their companies lagged peers in financial performance, none felt their organizations made better use of data than their peers.
In all, 76 percent of executives from top-performing companies cited data collection as very important/essential, compared with only 42 percent from companies that lag their peers in performance.
The differences are stark. But adopting a data-driven culture is not necessarily easy, especially for older companies that have achieved success with minimal use of data.
"Many of my clients are clearly aware of the importance of data," says Jerry O'Dwyer, a principal at Deloitte Consulting. "But they don't know where to start in termsof where they should focus to get the most value, as well as how to translate the data into actionable insight."
In some industries, executives may perceive a shift to a more data-driven approach as a threat. For instance, marketing has been the domain of creative for decades, but now, Giles says, it is as much a quantitative science as an exercise in art and design. Executives who built their careers on smart, instinctive decisions may perceive their value as declining as data's star rises.
Data-Driven Culture Requires a C-suite Champion
One of the most important steps, Giles says, is to break down data silos and promote sharing. More than half of respondents from top-performing companies said that promotion of data-sharing helped generate a data-driven culture in their organization. Such sharing does not arise organically. Someone in the C-suite needs to champion data-driven decision making and use top-down mandates and guidance to drive the shift in culture.
"Someone needs to see the appeal and step up," says Sidney Minassian, CEO of Contexti, a big-data analytics company that operates in the U.S., Australia and Asia. "It could be anyone from the C-suite."
More than two-thirds of executives from top-performing companies in the survey agreed, citing the importance of C-level leadership on data issues.
Of course, even with C-suite buy-in, there are challenges to integrating data use into the heart of an organization, not the least of which is training employees to leverage data and recruiting and retaining data specialists for tasks like predictive modeling. Nearly 70 percent of respondents said recruiting and retaining people who are effective at analyzing data is "somewhat" or "very" difficult. Underperforming companies, as well as companies in the Asia-Pacific region, rated the problem even more severe. Respondents cited lack of professional expertise among applicants, a shortage of analysts in their sector and high salary costs as the principal reasons for the difficulty.
Data-Driven Companies Democratize Data
However, the top-performing companies don't just leave data in the hands of specialists. They seek to democratize data use. Fifty percent of the top-performing companies said training employees to be more data literate is highly important.
Colin Hill, CEO at GNS Healthcare notes that in-house experts create the algorithms behind the tools it uses to assess the comparative effectiveness of different drugs, but the tools themselves are designed to be used by employees across the healthcare industry.
"Part of this is about making the complex simple," Hill says. "Computers are very good at the complex, but ultimately we have to break it down to the human level."
"Leading companies realize that being successful means giving people the opportunity to work with data," says Elissa Fink, chief marketing officer at Tableau Software. "Making data available and easy to use for all employees can transform an organization's culture. It's good for the company's bottom line."
Common Features of Data-Driven Companies
Ultimately, while there is no one path to becoming a data-driven company, those organizations that have achieved success do share some common features:
I'd like to revisit an old article which is still very relevant today. Thanks to Jen Underwood in SQL Server BI Blog for this amazing article.
Love it or hate it, export to Excel is still the most specified requirement in contemporary analytic tool selections, despite all the advances in business intelligence (BI) technologies. Excel is comfortable, flexible and with the new Microsoft Office 365 Excel Power BI add-ins (Power Query, Power Pivot, and Power Map), it's growing to become exponentially more powerful—pun intended. With the latest Microsoft strategy shift of embedding self-service BI applications right within Excel, could Microsoft's Excel Power BI release become a BI "killer app?"
Killer apps take the market by storm. Microsoft Excel was the spreadsheet killer app of the 90's, eliminating VisiCalc, Lotus 1-2-3, Quattro Pro, and many other players. According to Forrester's Q3 2013 Global Productivity Suite survey, Microsoft Office had a dominant, 95 percent or more, market share in the productivity software market. Through the years, we've seen a few productivity suites introduced including OpenOffice, LibreOffice, Google Docs, and Apple's iWork, yet none have been widely embraced. Over a billion people around the world use Excel today, far more users than any other BI vendor can claim. The Excel reach advantage is undeniably huge.
Microsoft Office Continues to Dominate
On the other hand, the inevitable shift to the cloud and browser-based apps, could level the playing field. In 2014, Google Docs is no longer a curiosity but rather a legitimate future threat to Microsoft Office. Google Docs has more chart options than Excel, a richer API, and predictive features already in beta. You never hear about Google Docs for analytics, but it's silently making a few substantial strides. Google and Microsoft are both mega-vendors. Anything is possible, but what is likely to happen?
In the Forrester survey cited earlier, most firms that deployed Google Docs are still using Microsoft Office due to employee needs, resistance to change, and technical compatibility issues with things like pervasive Visual Basic for Applications (VBA). Despite widespread marketing and increased interest in Google's offerings, Microsoft Office continues to dominate. Excel is still at the heart of business decision-making today and it's not easy to leave.
Excel in the BI World
Business users may love Excel, but in the professional BI world, it carries emotional baggage. Countless data warehouses and reporting applications have been built with the intent of removing Excel risks and dreaded "spreadmarts." Excel has been blamed for high profile analytic disasters such as JPMorgan's $6 billion trading loss. Keep in mind, people make mistakes using many tools—not just Excel—yet Excel remains an extremely popular attack target.
Quite a few data discovery and BI vendors advertise anti-Excel messaging in one way, shape, or form. Since Excel is where most analytic tasks are performed today, if Microsoft makes the right investment decisions and quickly executes, popular data discovery applications are most at-risk and they know it. Reviewing an admittedly subjective list of top requested data discovery tool capabilities, Excel Power BI already arguably meets or exceeds more than half of them and rapid releases new features monthly.
Data discovery requirements typically are business user driven requirements (a.k.a. the masses). If Excel can meet most data discovery requirements, why would you buy another stand-alone tool?
Currently, Excel 2010, the version most organizations have deployed, and even Excel 2013 Power BI, have key data discovery gaps. Excel 2013 is getting a lot better, but has not been displacing the data discovery vendors on a global scale, yet. Right now, Excel and Power View often get dismissed based on visualization capabilities, an area that users highly value in data discovery tools selections. Excel Power BI will continue to improve and could certainly become "good enough" for most business users in an organization if the visual analytics features vastly improve. Timely customer migration to latest Microsoft Office versions are a pre-requisite for potential data discovery market disruption. In the meantime, all of the data discovery vendors are in sales overdrive trying to secure as much market share as possible, offering free personal solutions and pitching a story of better analytics for the masses. To stay relevant in the future, they'll need to convert freemium users to paid subscriptions and offer much more compelling, differentiated solutions than they do right now.
An Exciting Time for BI Applications
It's an exciting time for Microsoft Office 365, Excel Power BI, and other BI applications. Looking ahead, we'll continue to see more innovations around information search, automation, cognitive contextual computing, predictive, and personalized analytics. Watching how those future trends are being brought to the masses via Excel and other platforms is fun! Office 365 Excel Power BI, Q&A, and Power Query are just the beginning of a significant BI and data discovery industry transformation. Controversial or not, Excel might not be the next killer BI app per se, but rather the next data discovery killer app in a few years. What do you think?
Organizations seeking to stay ahead of the competition recognize the importance of investing in analytics and visualization tools to deliver insights from their data. They need a modern, powerful business intelligence a(BI) platform that will stand up as an industry leader and a vision that will meet the needs of tomorrow.
Industry analysts have taken note of our efforts and we are excited to share Gartner has positioned Microsoft as a Leader, for the ninth consecutive year, in the Magic Quadrant for Business Intelligence and Analytics Platforms. For the first time, Microsoft is placed furthest in vision within the Leaders quadrant.
"The evolution and sophistication of the self-service data preparation and data discovery capabilities in the market have shifted the focus of buyers in the BI and analytics market -- toward easy-to-use tools that support a full range of analytic workflow capabilities and do not require significant involvement from IT to predefine data models up front as a prerequisite to analysis," Gartner said in its report. "This significant shift has accelerated dramatically in recent years and has finally reached a tipping point that requires a new perspective on the BI and analytics Magic Quadrant and underlying BI platform definition -- to better align with the rapidly evolving buyer and seller dynamics in this complex market."
Excel Power Query for Excel allows the Excel user to easily discover, clean, combine, and transfer data across a wide range of data sources including relational, structured and semi-structured. Power Query also provides you with the ability to perform online data searches to create powerful data mashups and create new intelligence from your data.
Power Map is a new 3D visualization add-in for Excel for mapping, exploring, and interacting with geographical and temporal data, enabling people to discover and share new insights.
A Trend Line is defined as a "Straight or curved line in a trend chart that indicates the general pattern or direction of a time series data (information in sequence over time)."
It may be drawn visually by connecting the actual data points or (more frequently) by using statistical techniques such as 'exponential smoothing' or 'moving averages.'
Trend Lines are extremely helpful because they allow you to review historical data and generate forecasts.
Excel Power View is a powerful new tool in Excel 2013 which will allow you to explore, visualize and present your data in ways you never knew possible.
Learn to use Power View to create powerful interactive graphical dashboards.
Power View is an interactive data exploration, visualization and presentation experience that improves decision making and gives you strong insight in to your business.
Power Pivot is an incredibly powerful tool that allows you to analyse more than a million rows of data as well as giving you the ability to link more than one table of data. See how easy it is to use.
Understand the power of Excel Pivot Tables and how they can make Business Data Analysis much quicker and simpler.
This is the first video in a five-part series showing you how powerful the Excel "Power" tools are and how learning to use them can vastly improve your productivity, while at the same time giving you insight into your business far beyond anything you ever imagined.
The Complete Introduction to Business Data Analysis teaches you how to apply different methods of data analysis to turn your data into new insight and intelligence.
The ability to ask questions of your data is a powerful competitive advantage, resulting in new income streams, better decision making and improved productivity. A recent McKinsey Consulting report has identified that data analysis is one of the most important skills required in the American economy at the current time.
This course focuses on the following different methods of analysis. During the course you will understand why the form of analysis is important and also provide examples of using the analysis using Excel 2013.
The following methods of analysis are included:
The Complete Introduction to Business Data Analysis is designed for all business professionals who want to take their ability to turn data into information to the next level.
If you are an Excel user then you will want to learn the easy to use techniques that are taught in this course. This course is presented using Excel 2013. Excel 2010 can be used for the majority of the training exercises. Small parts of the course do use Excel Power Pivot and Power View.
Please note that this course does not include any complicated formulas, VBA or macros. The course utilizes drag and drop techniques to create the majority of the different data analysis techniques.
For more information, visit our Online Course page
‘five seconds to sign up, five minutes to wow!’
Following six months of public preview and a continuous flow of user-driven enhancements from their rapidly growing community, Friday 24 July marked an exciting milestone for Microsoft as they officially released the new Power BI service and Power BI Desktop.
Over 500,000 unique users from 45,000 companies across 185 countries helped shape the new Power BI. Add to the already impressive list of features and capabilities the advances in their Power BI service and the new Power BI Desktop, there really is plenty to talk about!
But the news doesn’t stop there.
They’re rolling out new content packs weekly, ensuring that business users can connect to and experience data without technical setup. This means you can connect to your data in popular services like Quickbooks and Salesforce, and view a curated collection of dashboards and reports that continuously update with the latest data from these services.
They’ve also contributed their Power BI visualization framework and its complete library of visuals to the open source community to enable everyone to easily extend and build custom visuals. The project is available on GitHub, and in the future, anyone will be able to extend and bring custom visuals into Power BI for use in their dashboards, reports and content packs.
Power BI sets the standard for modern business intelligence and Microsoft wants us to change the way we do business and experience the cloud-hosted, business intelligence and analytics service for ourselves.
At the Boys & Girls Clubs of Greater Fort Worth in Texas they are using Power BI to understand how their various programs are performing and their impact on the kids they serve. With a mission to change each kid’s life for the better, the leaders at the Boys and Girls Club use Power BI to see their data update in real time and discover new insights visually to take action.
At Metro Bank in London, the team is using Power BI to quickly and accurately provide information to guide analysis and decision-making as they set out to create fans, not customers.
With all the new features and capabilities they’re delivering today, plus their commitment to a weekly release cadence, it’s time to join the 500,000+ people who have tried Power BI to start discovering the stories your data will tell.
Find out more, watch our Power BI Intro now.
Dates is one of the most common data types worked with in Excel. It’s probably also the most frustrating to work with!
Instead of a month, day, or year, Excel uses numbers to represent the date. It’s complicated further by the fact that dates are also days, like Monday or Wednesday but Excel doesn’t store that information in the cells.
In order to understand how dates work, you need to understand how Excel stores the information. It doesn’t use day, month and year but rather each date is assigned a serial number.
This serial number is created by using the total number of days from a specific date since the beginning of the year 1900. In other words, 1 January 1900 is assigned the number 1, 2 January 1900 is number 2 and so on. Of course, as time goes on, the numbers have gotten bigger and bigger.
Today’s date is 11 August 2015, the serial number assigned to it is 42227. So this means that there have been 42 227 days since 1 January 1900.
It seems confusing, but it makes it a lot easier to add, subtract, and count days. A week from today would be 18 August 2015, in order to work out the serial number you would need to add 7 to 42227 which will give you 42234.
The best part of this, though, is that you don't have to worry about working it out, Excel does all the work for you. All you need to do is make sure your dates are correctly formatted so that Excel can work with them as numbers, then you can do calculations and ask the questions you want to ask.
It is important to note that any date before 1 January 1900 is not recognized as a date in Excel. Excel does not work with “negative” date serial numbers on the number line.
To find out how to easily format your data, so that Excel recognises it as a date and not just text, watch our tutorial.
A common problem in Excel is the ability to calculate the age of a person.
In this tutorial we show you step by step how to calculate a person's age using the current date.
The business world has changed fundamentally over the past few years.The speed of change means that mangers need to be able to make decisions quicker, but at the same time need to be able to process far more data to be able to make an informed decision.
Business today has no lack of data – transactional systems record every part of the business process. The key challenge for managers is how to turn the vast quantities of data into useful insight and information.
In recent years new technologies has allowed vast quantities of data to be processed, analysed and increasingly visualised. The new technologies allow management to understand trends, identify outliers and to see new patterns in the data that could not be processed previously.
Excel as a Business Intelligence Tool
Excel has become the main tool for manipulating and analysing data, and developing reports in business today. With the release of Excel 2013, Microsoft introduced even more powerful business intelligence tools.
To understand how the Excel Business Intelligence tools work together in Excel 2013, you need to understand the function of each tool and how it fits into the “traditional” Excel model:
How is this relevant to Business Users?
If you work with Excel, using Spreadsheets and Tables to build reports, you probably find yourself regularly copying and pasting data between worksheets then clicking the same sequence of buttons to clean and shape the data.
You’ve probably found that complex formulas, dirty data and inevitable errors take up a lot of your time, making month end reports a nightmare that consumes large portions of time you don’t have.
Learning how to use all the Excel Business Intelligence Tools effectively will free you from the dull, repetitive tasks and give you time to focus on what’s important, analyzing your data and gaining insight into it.
This will enable you to make good decisions about your business.
PowerPivot is not new to you, it's just an extension of what you already know Remember, its Excel with PowerPivot, built by Microsoft. It's not a brand new tool set, it just makes Excel more powerful.
The size of a data set is no longer an automatic disqualifier of Excel as a tool for analyzing and reporting on it. You truly can take your Excel abilities up to the industrial scale.
It reduces the time you spend applying spreadsheet logic to other data sets, and maintaining sets of workbooks that all do basically the same thing. With better data capacity and performance, its suddenly an option to put everything into one master file and then filter as needed to focus on specific corners of the business.
Professional grade formula’s in PivotsThe feature that lets you add formulas (calculated fields) to your pivots has never been improved, it has remained limited, with nowhere near the richness of the formulas you can write in the regular Excel grid.
However, with PowerPivot, the engine has been revised and retrofitted so that you can use Excel formula syntax against it.
It's as if the Excel team spent years improving the Pivot calculated fields feature, and then sprung it on us all at once!