|
In my opinion, Pivot Tables are the most powerful feature in Microsoft Excel. They make aggregating, summarising and performing powerful data analysis quick and easy. Sadly, most seasoned Excel users have very little or no understanding of what Pivot Tables can do so the most powerful feature in Excel is also the most under-utilised. There seems to be a widespread misconception that Pivot Tables are difficult to work with, but this couldn’t be further from the truth. In this video I will show you how easy Pivot Tables are to use. You will see there are no complicated formulas and all I do is use drag and drop techniques to create management reports in a few clicks of the mouse. One of my most popular classroom training courses is a two days course on Pivot Tables and the amount of joy I see in people's eyes when they realise how much time they can save by simply changing the way they work is indescribable! In this video I have tried to summarise some of the “better” functions into a lot shorter time period but if you’re interested in finding out more, I really recommend signing up for one of my online or classroom courses.
I hope you enjoy the video!
1 Comment
Over the course of the next few weeks we're going to be releasing a series of videos in our "Complete Introduction to" series. The idea behind the series is to provide a comprehensive introduction to a variety of different data analysis and business intelligence tools and techniques so that business users can fully understand the tools available to them, and how they can work smarter not harder. The first video for the series will introduce you to Microsoft Power BI. One of the biggest selling points of Microsoft Power BI is that, because it's based on the Excel platform, the millions of Excel users working with data on a daily basis will find it easy to navigate and not be intimidated by this incredibly powerful tool. You will see how quickly and easily I produce an interactive report from an Excel spreadsheet using Microsoft Power BI. An Excel spreadsheet of Sales data has been used as the source of the data, but data from many different sources such as databases, .csv and Web sources can just as easily be used. In the video you'll see how easy it is to format tables of data, perform more advanced functions, like cross filtering, and add visualisations, such as column charts, line graphs and pie graphs, resulting in a detailed, interactive sales report emerging in minutes!The Microsoft Power BI Query Editor gives you a wide variety of options that allow you to transform, manipulate and prepare your data. It allows for perform simple and advanced tasks such as:
A big component of Power BI is it's unique programming formula language called DAX but don't be put off because, as I said at the beginning, Power BI is based on Excel, so many of the DAX formulas are the same and when you start using Power BI you should find yourself picking it up very quickly. At the end of the video I show you how to publish the report into the Power BI Service. The Power BI Service allows you to share the report to other users using a web browser, smartphones or tablets. I also show how to create a dashboard in the Power BI Service which makes it easier to access your content through mobile devices. Last but not least, I demonstrate how to use natural language queries (Q&A) to ask questions of your data. This powerful feature allows you to type in questions in a natural way and have Power BI provide the answers to them. I can ask 'What is my total Sales?' or 'What is my Sales in Italy?'and take it even further by asking 'What is my Sales by Product Category in Italy as a Column graph? This function fundamentally changes that way that users can interact and ask questions of their data. I hope that you enjoy this video and look forward to sharing the next one with you. If you are looking for Power BI training, please have a look at our Power BI Master Class and Excel BI Boot Camp.
Our previous post "Data or People? What is the most important resource in a business?" discussed the importance of developing your staff data analysis capabilities. Here we will outline the key steps in the journey to being able to use the latest technologies and techniques to produce reports and analysis. The following is focused on users who primarily work with spreadsheets and use Excel. Step 1 - Learn to use Pivot Tables Pivot Tables will speed up the time taken to produce reports and analyse your data tremendously. An important aspect of Pivot Tables is that you need to understand how to structure and format your data for Pivot Tables to work effectively. Step 2 - Create a Data Model New technologies such as Power Pivot and Power BI allow Excel users to easily create powerful data models. Data models allow you to easily centralise the storage of your data, calculations (business logic) and create relationships between the tables of data. Data models make it much quicker and easier to develop reports and speeds up your processes. Step 3 - Learn some Analysis Techniques Once you have an engine for the production of your reports, you need to learn how to ask questions of your data. There are many different types of analysis that can be performed on your data - comparison, trend, ranking, contribution, variance, frequency etc. are some examples. Add a bit of simple statistics and you will find a whole new world of interpreting and understanding your data. Visual analysis is a growing field of importance. Learn which graphs to use with which type of data. Visual analysis provides critical understanding of patterns and relationships within data. Step 4 - Time for New Data Technology such as Power Query makes it easy to access new data and to mash them up with existing tables of data. However the data may not always arrive in a format that is easy to use for data analysis. This is where you need to know how to use different methods to transform, append and merge data that creates powerful new data sets for different insights. Step 5 - Share your Insights Data analysis is of no use if we are not able to share the new insight and knowledge. Learning how to create effective reports and dashboards that can easily communicate key insights and information to your audience. Being able to tell a story about what your data means is a skill that will develop over time as your experience develops. Unfortunately often trial and error is required to find the best methods of what resonates with your audience and what does not. My advice is start simple with your presentations. Your audience will quickly tell you what they want. The ability to manage and use data in an effective and efficient manner has become critical to the continued viability and success of most businesses. Data is the key resource to informing decision making. It can be argued that data has become as important as cash when it comes to running a sustainable and profitable business in today's complex and uncertain business environment.
So why is it that so few businesses have a strategy and plan for the training and skills development of their knowledge workers to develop data expertise and capability? Many of today's knowledge workers spend countless hours working with data using obsolete technologies and techniques. I am continually amazed to see how many businesses are still running old style Excel spreadsheets with quadrillions of formulas in a multitude of sheets that nobody understands. A lot of business are running a lot of its processes through spreadsheets, and ;ets not kid ourselves, this a potentially very dangerous situation! Its probably fair to say that a lot of businesses are basically running on Microsoft Excel. So my question to them is how many staff members have been properly trained to use the full Excel suite, including Pivot Tables, Power Query, Power Pivot and Microsoft Power BI? We can even go a step backwards and ask how many businesses have trained their staff on the basics of data and visual analysis? My own anecdotal evidence from having trained thousands of people in the past 15 years is that not many people have received proper training in these technologies and techniques. I normally find that people have a basic understanding of Pivot Tables, which they either learnt from the person sitting next to them or as part of a course they did which just touched on them, and have little or no knowledge of Power Query, Power Pivot and Power BI. The reality is that data is useless unless it's transformed into information. I often see CEOs complaining that their companies are data rich, but information poor. So what should businesses do? I would recommend that the people who are knowledge workers and regularly work with data should be identified and then trained in this field to improve their skills and capabilities. We have proven that by simply changing the way you use Excel, you can become 80% more productive in 20% of the time. People who are trained to analyze data and that can ask meaningful questions of data are invaluable to any business who wants to make good business decisions that are driven by all the data they already have. These people are able to:
So in summary, data is useless without the people who have the right skills to understand how to work with it and businesses should therefore focus first on ensuring their people have the right skills and training to work with data before attempting to make data driven decisions. Regardless of what job you do, you will regularly make decisions, and in order to make these decisions, you need data. Data surrounds us and, whether we know it or not, data informs our decisions so being able to analyse data means being able to make better decisions. As technology has progressed, it's become easier to put systems in place to handle data, but it's also increased the amount of data we have available to us so we're constantly looking for easier ways to see the story behind the data. The introduction of computerised spreadsheets was a big moment in data analysis because suddenly anyone could input information, run a formula or create a graph and find an answer and as these tools have progressed, the access to information has exploded! As a trainer and consultant, I've chosen to focus on data analysis and using Excel and Power BI in order to analyse data. I spend a lot of time teaching people how to use these tools but I also spend time teaching different forms of data analysis because what a lot of people don't realise is that knowing WHAT questions to ask is as important, if not more important, than being able to put together an impressive dashboard. The two most basic forms of data analysis are comparison analysis and trend analysis so I'm going to start with them. Comparison Analysis A comparisons shows you things like what items sells the most or which product is not doing well. You can compare different months or regions to see where the biggest profit or lowest turnover is, you can also see how much of a difference there is between different products or time periods. Tables are the simplest way to do a comparison analysis, or if you'd like to visualise it then a column or bar graph is best. A pie chart is useful if you're only comparing two or three items but should be avoided for more than this. The column or bar provide a good indication of how large or small an item is in proportion to the other items that are being displayed. If there are a large number of items in the graph then a bar graph is a better option as the labels are displayed horizontally and are easy to read.
Trend Analysis Trend analysis is about understanding how data changes over time. Trends can be understood by many different time dimensions such as year, quarter, month, day of the month, day of the week, week number, hour and second. Understanding your data over different time periods will provide important insight into whether things are moving up, down, staying stable or is volatile. The analysis of different time periods allows you ask a variety of important questions such as:
When working with trends you should use a line graph or area graph. The line graph will provide a good understanding of the general trend over time. A trend line is often added to the line graph and provides a good visual indicator of the trend. An area graph is a good option for when you want to include a comparison and see how it is trending. For example an item could start off small, but grow over time, an area graph is a good method to display this information.  In the fourth video of our Power BI series we will show you how to easily edit data in Power BI. Discover how Power BI makes data analysis much simpler! 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, LoadPower Query is an intuitive, easy to use tool for importing, transforming and working with data. Data Manipulation. Data ManipulationSpreadsheets 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. PresentationPivot 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:
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 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. |
