DATA INSIGHT TRAINING
  • Home
  • On-Site Training
    • Power BI Master Class
    • Excel BI Boot Camp
    • Interactive Dashboards with Excel Pivot Tables
  • Online & Virtual Training
    • Our Courses
    • Affiliate Courses
  • About Us
  • Blog

5 step plan to develop your data analysis capabilities

1/11/2019

0 Comments

 
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.
0 Comments

Working with dates in Excel

1/8/2017

0 Comments

 
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.
0 Comments

Calculating Age in Excel 

1/7/2017

0 Comments

 
​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.
0 Comments

    Categories

    All
    Business Data Analysis
    Complete Introduction To
    Coronavirus
    Data Skills
    DAX
    Excel Dashboards
    Excel Formulas
    Gartner Report
    Inspiration & Motivation
    Pivot Tables
    Power BI
    Power BI Desktop
    Power BI Query Editor
    Power BI Services
    Power Map
    Power Pivot
    Power Query
    Power View
    Tables & Reports

    Archives

    April 2020
    March 2020
    February 2020
    January 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017

Privacy Policy
Copyright datainsighttraining.com
info@datainsighttraining.com
+44 330 223 5910
  • Home
  • On-Site Training
    • Power BI Master Class
    • Excel BI Boot Camp
    • Interactive Dashboards with Excel Pivot Tables
  • Online & Virtual Training
    • Our Courses
    • Affiliate Courses
  • About Us
  • Blog