|
Excel is an amazing tool for analyzing data, but the challenge is understanding what your data is telling you. A problem I find people regularly battle with is that when you start exploring different aspects it’s easy to end up with Analysis Paralysis and getting overwhelmed by how much information is available. In this video I will show you to create powerful interactive dashboards using nothing complex, just simple drag and drop techniques, which will give you the big picture as well as the ability to filter and sort as needed.  Dashboards are the ideal tool for providing a high level overview of what is happening in the business, department, team or project and are traditionally made up of tables of data and graphs that can quickly and easily convey important information. Before starting to set up a dashboard, you need to decide on your key metrics. These are normally important totals that you want to share, for example, total sales, total profit or average profit per customer. I suggest that the Key Metrics are bold and placed in the top right area of the dashboard as this is where most people will start viewing your dashboard.  Visual elements are another important element of a dashboard as graphs convey a lot of information very simply. A trend graph easily communicates the general trend of data, is the data moving up, down, stable or volatile? Line or area graphs are normally used to communicate trends.  Column and bar charts are used to communicate which items are highest or lowest. These types of graphs provide an understanding of magnitude differences between items and are used to communicate comparisons.  Pie charts can be used to communicate percentage contribution to the whole. It is easy to overcrowd a pie chart by using too many items - so the general rule of thumb is to keep the number of items down to no more than 5 to 7 items. Colour also plays an important role because they convey subconscious messages. When designing dashboards, I suggest not using too many colours and to keep in mind the impact of the colours you choose (ie. green is associated with good and red is associated with bad). My advise is to use use soft grays, browns or blues for backgrounds and bold colours for the key things you’d like to highlight.  A key point I’d like to bring your attention to, is that while I’m going through the processes of how to create an interactive dashboard in Excel using only Pivot Tables, Pivot Charts and Slicers, I never use complex formulas, VBA or macros. Microsoft have really moved away from these complicated methods and made things simple for all Excel business users.
I hope you enjoy this video and if you are interested in learning more about creating interactive dashboards with Excel, please have a look at our on-site classroom courses and online or virtual courses.
0 Comments
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! 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.
One of the most common challenges that faces Excel users is having two separate sets of data that need to be referenced for reporting purposes. In this video I show you how to use Power BI to create a relationship between two spreadsheets in order to create a report using information from both. I've used a common challenge which many HR professionals encounter, where there are two sets of data, an employee training file (which records all training done by every employee) and an employee master file (which has details for each employee such as department, gender, marital status etc). I show you how to create quick reports and graphs to easily visualise information such as total training cost by department or percentage training costs by gender. In Excel, these reports traditionally take a long time because they require complicated formulas and VLookUps but in Power BI it's done in a few simple clicks.
For more information on our training, please look at our classroom training courses. 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. Every year, Gartner releases their annual Magic Quadrant for Analytics and Business Intelligence platforms. In the report, they evaluate the strengths & weaknesses of the main service providers in the marketplace as well as providing a graph plotting these providers based on their ability to execute and their completeness of vision. For the past 12 years, Microsoft has been slowly moving higher, but since 2016, the movement has been significant, with them being one of the leaders for the past 3 years and now pulling ahead to take the lead in 2019.
Since it's release in 2014, Power BI has rapidly evolved and grown to become a formidable challenger in the analytics and BI sphere. What a lot of people don't realise is that Microsoft is offering an ecosystem, not just Power BI. Low cost of entry has contributed to the high adoption rate of a product that is only 4 years old. Anyone can use Power BI Desktop for free. This single user offering includes data cleaning and preparation, custom visualizations and the ability to publish to the Power BI service. The Power BI Pro plan includes data collaboration, data governance, building dashboards with a 360-degree real-time view and the ability to publish reports anywhere and includes a 60 day free trial. Microsoft have also bundled it with the Office 365 E5 version, as well as embedded it in Microsoft Azure, so there really are a huge number of options available with total implementation costs being significantly lower than the competition.
Looking forward, Microsoft is now putting a lot of focus onto "Pervasive AI using Power BI". Their advancements in AI are being integrated into their BI stack for generic use, for both citizen data scientists and business analysts. Power BI’s automated capabilities with AI-infused experiences such as natural language, quick insights, image recognition, text analytics, and key driver analysis are new and has leaving the competition behind. They are leveraging the innovation from Microsoft Research with heavy investments into these integrations and constant updates. Add to this the frequent updates (Microsoft has been releasing monthly updates for the last 12 months) in a wide spectrum of categories, including reporting, modeling, analytics, data connectivity, data preparation, Power BI service, Power BI mobile and Power BI embedded as well as the investment in a broad set of visionary capabilities and integrating them with Power BI (examples include enhancements to augmented analytics, new AutoML features available in Azure Machine Learning and new Azure cognitive services) it's clear that they are committed to keeping their lead! In a nutshell, there are a number of reasons, aside from ease of use (which users gave top-third ratings across all aspects for) and cost that so many of the estimated one billion Excel users worldwide are adopting Power BI as their go-to BI and analytics tool. So far we have looked at comparison analysis, trend analysis, key metrics analysis and ranking analysis. The last form of analysis I would like to look at is contribution analysis.
Contribution analysis is about understanding what the percentage contribution of each item is and how it contributes to the total. The reality is that the human brain is not great at understanding large numbers, so it's much easier to understand percentages. Percentages are always done in the context of a the total being 100%, so it's very quick to see whether a percentage is a smaller amount like 10% or a large amount like 70% regardless of what the actual numbers are that are used to get the percentage. The pie graph is probably the misused and abused visualisation and I generally steer clear of using it, however one place that it can be used appropriately is for contribution analysis and to display the proportion in relation to the whole. But whenever I teach on this, I always give a few rules to follow when creating pie graphs. These are:
The next types of analysis I'm going to focus on are Key Metrics and Ranking Analysis.
Key Metrics Key metrics displays one key piece of information prominently (examples include total sales, total profit or total number of customers) and by using ratios you can get deeper insight and more information from your key metrics. For example we could create key metrics for displaying the profit ratio (which is the total profit divided by total sales) where the profit ratio is displayed as a percentage, this makes it easier to see what portion of your sales is profit. Another ratio that can be calculated is the average sale per customer or the average profit per customer. In this case you would divide the total sales by the number of customers and the total profit by the number of customers, so by using three key metrics you have now created three new key metrics using ratios which gives further insight into your data. Ranking Analysis Ranking analysis is about understanding the top/bottom values within a range. The simplest form of ranking is to do a sort from highest to lowest or lowest to highest but there are many more rankings that can be done. Top 10 or bottom 10 is one of the common method of analysis to highlight the items that are contributing the highest or lowest within your data set. Filtering your items to only show the top 5, 10 or 20 makes it easier for you or your audience to view the most important items in a table, rather than displaying a long list of items. A top 10 or bottom 10 analysis can also be combined with your column or bar graphs to restrict the number of items that are displayed in the graph. You can also use a top/bottom percentage filter, in other words limiting the list of items to the top 20% or bottom 20% and this again easily allows your audience to focus in on the key values within the data set. 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.  As a small, niche training company, our journey has gone along many paths and taken us across continents! We started in South Africa and then emigrated to the UK at the end of 2018. As part of the new chapter, we decided we need to rebrand and refresh so ExcelBusinessIntelligence.com has changed to Data Insight Training. One of the reasons for the change is that Google has been doing some really interesting development on their data analysis tools and Google Data Studio is definitely becoming a player in the field of data analysis, but more about that in another blog! For now, I'd like to introduce our new logo, we hope you like it as much as we do.   What did he mean?A young man approached the foreman of a logging crew and asked for a job. “Let’s see you fell this tree first” said the foreman. The young man stepped forward and skillfully felled the large tree. The foreman was impressed and said to the young man: “You can start on Monday.” Monday, Tuesday and Wednesday came and went. On Thursday afternoon the foreman came to the young man and said: “You can pick up your pay-check on your way out today.” Startled the young man exclaimed, “But I thought you paid on Friday!” “That’s right,” said the foreman, “but we are letting you go today because you have fallen behind. Our daily felling charts show that you have dropped from first place on Monday to last place today.” “But I work really hard,” said the young man. “I arrive early, I leave late and I even work through my breaks. Please don’t just fire me.” The foreman knew this to be true, and sensing the young man’s integrity, stopped and thought for a bit. Then he asked: “Have you been sharpening your axe?” The young man replied: “No sir. I have been working too hard to take time for that. Work smarter, not harder.At DataInsightTraining.com we will show you how to make data work for you!
In the fourth video of our Power BI series we will show you how to easily edit data in Power BI. Power BI has the ability to create Tables and Reports in minutes, which can then be shared via the cloud. Power BI Desktop puts visual analytics at your fingertips with intuitive reports and dashboards. Using drag-and-drop to place content exactly where you want it on the canvas allows you to quickly discover patterns as you explore a single view of linked, interactive visualizations. Power BI for Office 365 is Microsoft's latest Business Intelligence solution. It is cloud-based and works from within Excel and Office 365, to analyse and visualise data quickly and easily. The BI solution is designed to help business users gain insights from their data and, according to Microsoft, includes the following: - Power Query - Power Map - Power Pivot - Power View 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.
|
