Microsoft Power BI: Super Charge Your Data Analysis Process

Do you analyze data? Whether you work in Sales, Finance, Manufacturing, Logistics, Engineering or Customer Service, there is a constant need to analyze data.

With the advent of the industrial age and the conveyor belt, gaining efficiency and reducing  errors has been a constant battle. We need more and better ways to analyze data. Today, billions of new data points are gathered every minute. There is more storage available in the cloud, and there is no dearth of computers big and strong enough to process it quickly.

Yet, there is a lack of know how, a lack of understanding on how to analyze data. As a result, more companies are still not able to tap on the promise of Big Data, Artificial Intelligence, Internet of Things or Machine Learning.

To Understand the State of Data Analysis, and look at solutions, we need to understand a few fundamentals about Data, and Data Analysis.

Why Do We Want To Analyze Data?

Duh!

Of course there are a gazillion reasons.

No… Not really. The need to analyze data is routed in only a few basic reasons.

  1. To Gain Insights about our data. Why?
    • So that we can find out what works, and what does not. So we can compare the past results with another company, anoder product, another period, and see if there is some insight to glean.
    • When we find out what works, and find out where the problem lies, we can then perform Root cause analysis – which can tell us the real problem to fix. When we have fever, we can take a tablet of Paracetamol (Tylenol/Panadol/Aspirin). However, the fever is not the disease. It is a symptom that we develop, when something is not right with our body, and the body is fighting the bad bacteria/virus. During this fight, the temperature rises. The Paracetamol can reduce the fever, but it does not remove the fight. To go to the root cause of the problem, a visit to the doctor is required. The doctor may analyze the problem by looking at your Blood pressure, heart rate, x-ray, probes etc. and figure out the issue. If it is indeed bad bacteria causing some area to swell, the doc can issue a dose of Anti-biotics, which fight the bad guys, and treat the real problem. Once the bad guys are gone, the fever automatically comes down to the normal level.
  2. Make Better & Faster Decisions
    • Based on the insights, we are able to make better decision, and faster too.  And backed by data, we can have more confidence on our decision making process… rather than relying only on the gut-feeling.
    • With statistical methods of analyzing data and forecasting trends, the accuracy of the analysis, and insights gets deeper as we generate better analytics. We can then make a more informed decisions faster.

How Do We Analyze Data: The Traditional Data Analysis Process

The data analysis process is simple, and we have been following it for so long that it has almost become a habit… albeit a not so good habit. What we usually do is outlined below:

  1. Get the Raw Data File From The Source System
    • This could be a Text File, a Comma Separated Values (CSV) File, a PDF file, an SQL data dump, an Access or RDBMS source (think Oracle, Microsoft SQL Server or MySQL Database)
  2. Load into Excel
    • Load this data into Excel. This could be easily achieved, but this is a manual process, that has to be repeated every day, week, month etc., whenever the data changes, or new data arrives.
  3. Clean
    • Incoming data is seldom clean. There will be missing values, duplicates, wrong headers, mis-aligned dates in YYYYMMDD or some other odd text or  numeric layout, and other data issues to fix.
    • To do this, you’ll have to write some simple formulas, functions, data cleanup steps, fix dates, and fill blanks or nulls with an appropriate values. This is all manual process, unless automated with the help of macros.
  4. Lookup Master Data
    • Once the data is cleaned-up, we need to change the codes with values, get the correct product price, employee salary, or the date of manufacture from some master data tables. This often requires expertise with Excel formulas like VLOOKUP & HLOOKUP. You may have to do some interim calculations too, create some calculated columns, and create a wide table with all the fields/columns required for the analysis in the next step.
  5. Create Pivot Tables
    • To analyze the data, one of the quickest and fastest ways is to create a Pivot Table. Pivot Tables help you to summarize data – create Sum, Count, Differences, Cumulative Totals etc. and split it by the Rows & Columns. Further, you can create multiple Filters or Slices using the Filters or Slicers options.
  6. Keep Refreshing
    • By default, a Pivot Table does not refresh automatically. You have to either set up this option, or refresh manually. And even if you refresh it either way, it may not pick the additional, new data, for the range of the source data may be set explicitly to an Absolute range. Changing the Absolute range always is an extra step that you have to remember to do.
  7. Create Charts
    • To present the data, we mostly rely on the popular Bar Charts, Column Charts, Pie Charts, and the newly introduced Map, Funnel or Tree map charts. However, charts often get too busy, and there are not too many options to customize them. Once the charts are ready, you don’t want to send the entire Excel file, with the charts. And sending only the charts is not an option, without the underlying data. This brings us to the next point…
  8. Paste Charts Into PowerPoint
    • PowerPoint is the darling of the corporate boardroom. No presentation is ever complete without a mega slideshow. So people use Excel to analyze the data, and the paste the charts into PowerPoint. They create hundreds of slides, and then marvel at their slide deck. Finally, they are ready for the big presentation to the directors.
  9. Present to Management using PPT Slides
    • The PowerPoint show begins, and your audience is thrilled by your analysis, prestation and charts. You are secretly gloating over the success. Suddenly a member of the audience has a question – can you compare this quarter with a quarter 5 years ago?
    • Well… yes you can. But you don’t have the data right now. You’ll have to get back ot your desk, do the right analysis, and probably show them the chart in your next quarterly meetup.
    • The opportunity window is so small, by the time you get back to them, they probably do not need the information any longer. You have to cut a sorry figure, and all your great work comes to nothing…

What’s wrong with this whole scenario. Let’s find out.

Problems With Traditional Approach To Data Analysis

There are a number of major problems in the Traditional approach of Data Analysis that has been around for the past 20-30 years… pretty much all along the advent of Spreadsheets. To name a few:

  • Excel Charts and Reports Are Not Interactive
    • Traditional Excel or PowerPoint Reports/Charts are not interactive. They are just screen shots. So they don’t change dynamically. Good because it will always be the same, and speed of execution will be fast. Bad because it can’t be used again… And has to be manually refreshed each time.
  • You Can’t Share Excel Files Easily
    • People are worried when it comes to sharing their Excel files. They have security, and privacy concerns. And an Excel file is quite fragile. Change of any path, sheet, formula or cell can break the workings completely, rendering it completely useless.
  • Excel VLookups are Slow & Cumbersome To Use
    • Excel files rely on Vlookups to pick the correct employee salary, product description attributes, or prices. It works great, and has been the mainstay for Excel’s popularity. However, VLookups recalculate extremely slowly on a linked Excel file, which has anywhere in excess of 200,000 records. At half a million, things absolutely come to a standstill, and many times Excel will give you the dreaded Blue Screen and simply crash.
  • Excel Takes Time to Clean & Refresh every time
    • Newly added data has to be cleaned up. It takes time, and is a slow, painstaking process. If you forget to update, or refresh on time, it shows old/wrong data and is a complete waste. New data has to be added, and refreshed each time.
  • Security of Excel Data is Quite Fragile
    • Sending Excel reports without the data is simply not possible. And sending it with data raises Security concerns. Any leaks will destroy your pricing, and lose your competitive advantage. And any broken links will render the data useless too.
  • A Very Small, Finite Limit of Excel Rows
    • There is only a finite number of rows that can be loaded into Excel. The current limit is a million rows (1,048,576 to be exact.). But in today’s world, a million rows is considered nothing. We need to look at ways to expand the limit to millions of rows.
  • Pivot Table Limitations
    • Pivot tables are quite slow to refresh. Further, they do not refresh automatically. And, there are very few ways to visualize the data – only sum, count, average, and YTD etc. It lacks the rich data transformations like Year on Year, Quarter on Quarter Analysis. It is not very good at showing the total overall percentages and its breakdowns.
  • Wasted Time in Cleanup
    • 80% time is spent  to clean the data, and only 20% is left for us to analyze it. This does not accord the time and importance data analysis requires. The equation is completely skewed out. It should be the other way round – 20% to clean, and 80% time for analysis.

Is There A Solution To All These Data Analysis Woes?

With the current state of Business Intelligence Tools, there is more than a glimmer of hope. The industry has finally arrived to a point where Big Data processing is becoming commonplace, and is no longer in the realm of the wealthy or the academics. Today, the common man’s BI tools are already working wonders.

  • Need for Interactive System
    • Today, the top of the breed BI tools like ClikView, Tableau and Microsoft, all offer dynamic ways to present data, and interactive ways to visualize it, in numerous ways.
  • Write Once. Use Again.
    • Now the steps of cleaning the data can be recorded, and used again and again, each month with hardly any tweaks. This reduces the need for manual cleanup and improves accuracy and reliability. It offers ways to scale up the data analysis process and work on more value added services.
  • Shift Cleanup vs Analyze Ratio to 20-80.
    • With the added interactivity and automation, finally more time can be spent in analyzing the data. Cleanup jobs are set in the background, and they can continue to run on auto-pilot. Plus, we are able to process data now in real time, which drastically improves the prospects of having usable, actionable data and insights.
  • Load Data From Multiple Sources
    • The ability to load and merge data from multiple sources is becoming much easier, and automation is making this chore into a breeze that is a fun to do activity today.
    • Data from Text files, CSV, Excel, Databases, XML, Websites, Live Tickers, On and Off site Corporate Data Warehouses is becoming a reality. This opens up new possibilities.
  • Remove need to do complex, slow VLookups
    • Forget slow VLookups, and be able to lookup any row, any column, irrespective of the order or distribution of the data rows and columns. This frees up new options and makes merging data a simple matter. In fact, now we do not need to have very wide tables.. We can have shorter (Less wide – less number of column in a table And  have taller tables (More data rows)
  • Load Huge Data Volume, beyond 1 million rows of Excel
    • Options like Microsoft Power Query & Microsoft Power Pivot enable you to load a few million rows a simple matter. And there is no need to worry about running out of space in Excel or its bandwidth – the ability to process a few million rows.

Microsoft Power BI: The Magic Wand To Vanquish All Your Data Analysis Problems

  • Super Fast
  • Dynamic, Interactive
  • Handle Big Data With Ease
  • Hundreds of Data Sources
  • Build Relationships. End of VLookups
  • Multiple Ways to Refresh
  • Visualize First
  • Generate Insights
  • Share With Ease
  • New Ways to Visualize – Maps, Tree Maps, Funnels, KPIs, Speedometers
  • Security of Sharing
  • Multi Device Support – Web, Desktop, Mobile, Tablet, Without installing any additional software
  • Real Time Processing
  • Multiple Dashboards
  • Slice & Dice To your Heart’s content

Conditional Formatting in Microsoft Excel

Microsoft Excel training at Intellisoft

In Microsoft Excel, new features are sparklines and slicers, and improvements to PivotTables and other existing features, can help us to discover patterns or trends in the data. In the previous post we had a look at the Sparklines and slicers features of Excel 2010 and Pivot table features of excel 2010, so now we will look at the improved pivot table feature of excel 2010.

Improved conditional formatting

Conditional formatting makes it easy to emphasize important cells or ranges of cells, highlight unusual values, and visualize data by using data bars, color scales, and icon sets. Excel 2010 includes further more formatting flexibility.

New icon sets: In Excel, we  can access to more icon sets, including triangles, stars, and boxes. We can also mix and match icons from different sets and more easily hide icons.

For example, we might choose to display icons only for high profit values and remove them for middle and lower values.

More options for data bars: Excel 2010 comes with new formatting options for data bars. You can apply solid fills or borders to the data bar, or set the bar direction from right-to-left instead of left-to-right.

Not only that, data bars for negative values appear on the opposite side of an axis from positive values.

excel trainingIf you would like to learn more about these new features of Microsoft Excel 2010 or 2013, or would like to attend the Microsoft Excel Training, do contact us at Intellisoft Systems.

If you have any further questions then contact us through email training@intellisoft.com.sg or call at +65 6296-2995!!!

Trainer: Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft. He has trained over 5000 students in over 18 countries, and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.

New Features in Excel

Do you know that Microsoft Excel 2013 comes with a plethora of new features. It is worth investing the time to learn these features, and make the most of the latest and greatest spreadsheet software by Microsoft.

So what is so great about Microsoft Excel 2013? Plenty. Our Master Trainer, Vinai Prakash take you to a tour of the new features in Excel 2013.

1. Quick Analysis Tool: The new Quick Analysis tool allows you to convert your data into a chart or a table in just two steps. Preview your data with conditional formatting, spark lines, or charts, and make your choice stick in just one click.

2. Instant Flash Fill: Flash Fill enters the rest of your data in one fell swoop, following the pattern it recognizes in your data.

3. Chart Recommendations: With Chart recommendations, Excel recommends the most suitable charts for your data. Get a quick peek to see how your data looks in the different charts, and then simply pick the one that shows the insights you want to present.

4. New Functions: There’s a whole slew of  new functions in the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories.

5. Smart Pivot Tables: In Excel 2013, When you create a PivotTable, Excel recommends several ways to summarize your data, and shows you a quick preview of the field layouts so you can pick the one that gives you the insights you’re looking for.

In the new Excel Data Model, you’ll be able to navigate to different levels more easily. Use Drill Down into a Pivot Table or Pivot Chart hierarchy to see granular levels of detail, and Drill Up to go to a higher level for “big picture” insights.

6. Improved Collaboration Tools: Working with other people on shared files in real time is a double-edged sword. While it’s useful to do this, you will face problems when two people try to change the same item at the same time. In Excel 2013 you can share and work collaboratively on files with others via SkyDrive using the Excel WebApp, and multiple people can work on the same file at the same time.

7. Standalone Pivot Charts: A PivotChart no longer has to be associated with a PivotTable. A standalone or de-coupled PivotChart lets you experience new ways to navigate to data details by using the new Drill Down, and Drill Up features. It’s also much easier to copy or move a de-coupled PivotChart.

Learn Excel 2013 in 2-3 Short Day Training: With so many new features, it is important that you learn these features, to improve your efficiency, productivity, and make use of these new  features. After all, what is the point of using the latest software if you do not use its latest and greatest features.

Learn to use Excel 2013 features like Vlookup, Macros, Pivot Tables, Pivot Charts, Tables, Advanced Functions and Formulas, Sharing, Collaboration, Removing Duplicates, and much more…

Do contact us at Intellisoft to attend our Basic Excel 2013, or Advanced Excel 2013 training courses at our training centre, or have a Customized Corporate Training on Excel 2013 at your office.

We also provide WSQ Funded courses for Excel 2013, which means that up to 95% of the course fee is funded by the Singapore Government. Terms & Conditions apply, so contact us for more information on training fees, Grants and customized solutions for your company.

New Features in Microsoft Access

Practical hands-on MS Access training in Singapore

If you’re searching for a more flexible data management system, a database might be just the salvation you’re looking for and Microsoft Access 2013 provides an excellent option. With Access 2013 you will experience new interface with different look and feel. It has got sleeker look and it has more colors to make it more modern style.

You can not only save the document which you can access anywhere but at the same time you can collaborate it with other people.

Microsoft Access 2013

Features of Access 2013

Access 2013 has changed the tabs of ribbons and made it capitalized which was not there before.

Also if you have not worked with SkyDrive before that’s something which is going to be new for you. Want to explore this?

So when you are trying to open any new or existing document you don’t only have a option of choosing it from Recent but also you can also select it from SkyDrive.

After entering your account details it enters into your SkyDrive and then you can browse your database the same way as you browse in windows explorer. Like downloading we can also upload our local database to the SkyDrive.

Access 2013 has moved towards the Cloud and can now produce Web Apps which can be accessed through a browser.

There’s a quantity of Wizard help available in constructing these, so you’re not working from the skretch up when constructing one. Navigation and different views are pre-constructed, as long as the Web App you’re after can be based on one of the database templates provided.

Here are the top features you should explore in Access 2013.

  • Using Templates in Access 2013
  • Apps in Access 2013
  • A Focus on the Web: Office 365, SharePoint 2013, and Access 2013
  • SQL Server: Behind the Scenes of Access 2013

If you would like to learn more about these new features in Microsoft Access 2013,

or would like to attend the Microsoft Access Training, do contact us at Intellisoft Systems.

Avoid Crowded Worksheets with this button

One simple way to reduce worksheet crowding is to rotate your column labels so that they read up, down, or vertically.

Add them to the Format toolbar, as follows:

  1. Choose Tools + Customize.
  2. Then Click the Commands tab.
  3. Under Categories, choose Format.
  4. Under Commands, find the Vertical Text button and drag it into place on your Formatting toolbar.
  5. Repeat Step 4 to drag the Rotate Text Up, Rotate Text Down, and, if needed Angle Text Upward and Angle Text Downward buttons to the Formatting toolbar

Now, whenever you want to angle or rotate text, just select the cell(s) and click the appropriate button. It will save you space, and make it easier to read and avoid eye strain

Sparklines Microsoft Excel

Practical hands-on basic Excel training

In Microsoft Excel, some of the new features are sparklines and slicers, and improvements to PivotTables and other existing features, can help us to discover patterns or trends in the data. To get started with the features of Excel, first we will look at the details of the  Sparklines and slicers features of Excel.

Sparklines

Sparklines are tiny charts that is used to fit in a cell to visually summarize trends beside the data.

Since sparklines show trends occupies less space, they are exclusively useful for dashboards and other places where we need to show a glimpse of the business in an simple practical visual format.

In the image to the left, the sparklines that appear in the Trend column lets us have a quick look of the performance of each department in the month of May.

Slicers

Slicers are visual controls. They let us quickly refine data in a PivotTable in an interactive, automatic manner. If we insert a slicer, we can use buttons to quickly segment and refine the data to display appropriate results.

Not only that, when we apply more than one filter to the PivotTable, we no longer have to open a list to see which filters are enforced to the data. Rather, it is displayed on the screen in the slicer.

We can make slicers relate to the workbook formatting and easily reuse them in other PivotTables & PivotCharts.

excel trainingIf you would like to learn more about these new features of Microsoft Excel 2010 or 2013, or would like to attend the Advanced Microsoft Excel Training, do contact us at Intellisoft Systems.

If you have any further questions or want to join a training on how to use Sparklines, contact Intellisoft for Corporate Training on Excel 2010 or call at +65 6296-2995

Trainer: We have certified trainers who excel in imparting their knowledge and are very patient. Master Trainer Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft.

He has trained over 5000 students in over 18 countries, and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.

Using Pivot Table Data in Summary Sheets

Learn Pivot Tables Easily

Pivot tables are a great boon in Excel. Previously, getting IT departments to write custom reports used to take ages. And even if you could get a report written for your needs, by the time you got it, it was too late, or you wanted to look at information from another angle.

With Pivot tables, you can now create your own reports in no time. And you can slice and dice information pretty easily, with just a few clicks.

However, even though Pivot tables are great, they are not the best tool  for presenting information for the senior management. There may be times when you want to pick up certain information from the pivot table, format it nicely, and present it with other summary figures.

To use the summarized data from the pivot table, but make it more presentable,  you can use an extremely useful function of Excel called the GetPivotData.

Enabling GetPivotData Button
It is very easy to get this function to work. This little gem is hidden right within the Pivot Toolbar. Just right click on the Pivot Toolbar, right at the end, and select customize. Pick Add/Remove Buttons.

Select GetPivotData button. This is a toggle button – click it once and it gets enabled, and another click disables it. You can see a slight change in the icon when it is enabled or disabled.

Once the button is highlighted, you can begin writing your formula. Start with a = sign in a black cell where you want a pivot table value. then point to any cell in the Pivot Table. Its value is captured in your formula.

As long as the data is available and visible in the Pivot table, you can move the data around from rows to columns or page fields, but it will still appear correctly in the presentation area.

Go ahead. Give it a try and make your data presentation summaries more dynamic and presentable. Any questions or comments, do let me know.

Cheers,
Vinai
Editor, Microsoft Excel Tips Blog

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!

Found What You Were
Looking For?

Just Tell us...

We're Here To Help You!