Discover how Power BI makes data analysis much simpler!
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.