Data Import to Excel Workbooks

 

Data Import to Excel Workbooks

Data, millions of bits of data, in one huge amorphous mass or fragmented across multiple systems.  How to grab this and make sense of it?

  • How many hours have been spent copying and pasting information from one spreadsheet to another?  
  • How many times have data dumps been taken from systems in the form of CSV files, proving unmanageable and containing excessive amounts of irrelevant data, duplication or error? 

There are many specialist applications out there that will take your data sources and swiftly churn out ever impressive trends, graphics and insights.  They are, however, expensive and not often  available to all employees.  A small business might not have the resources to commit to specialist packages.

It's okay you can do so much in Excel with the right knowledge, training and awareness of capabilities.

For example did you know?
  • You can set up a connection between your database and an Excel workbook, no need for separate and obsolete data dumps, no need to import each time
  • You can set up connections to other Excel workbooks to segregate and limit access to the information
  • You can set up advanced queries that will filter and sort the data before it is even imported into your workbook
  • You can combine data sets either as one large table (merging) or adding additional rows of data (appending)
  • You can set an automatic refresh so that your data is bang up to date
  • You can set your workbook up so that it imports directly into pivot tables and charts, which in turn can be linked to PowerPoint presentation

Excel is a powerful tool that is capable of taking raw data, manipulating it and then presenting it to get meaningful information in order to inform decisions or to demonstrate trends.  The big question has to be how does Excel get the data in order to be able to do this creative work?

Well imagine having to type in hundreds and thousands of rows of data, with the likelihood of error and cost to time to the business. 

Clearly it is better if we can just get a download from a database or application, often in the form of a report and then import it into Excel.

Better still if we can create a live link to the source of the data and merely refresh it so that it is bang up to date, this in turn can go directly to the pivot tables and graphics.  The time savings are significant.

So the good news is we can do this and what is more, we can tailor the connection to discard unwanted bits of data and tidy up some errors.

If there is an application then there will be a dataset that sits behind this and can therefore be mined for useful information.

The video below will take you through the steps to import from a database, CSV or Excel file.


Chapters:

00:00 Introduction
03:42 Import CSV
09:37 Power Query (CSV)
16:36 Power Query Load Data
19:00 Power Query Add Conditional Column
22:32 Power Query Refresh
24:06 Import Excel File
26:29 Power Query (Excel)
28:28 Power Query Load to PivotChart
31:11 Import Database
33:32 Copy Power Query

Feel free to get in touch if you want advice or would like to schedule training.

Comments

Post a Comment

Popular posts from this blog

NKW6 PT1 - IF

Image
Introduction I recently pulled together an Excel session to go through the fundamentals of the IF, VLOOKUP, INDEX and MATCH functions.  To do this I started out by putting together a bit of an explanation and then recording, with sample data, how these functions can be used and common pitfalls.  This is the first session. Part 1 IF The IF function can allow you to interrogate the information in a cell or cells and then instruct the programme to return a response, which can be text a number, a cell reference, another function or a calculation.  It sounds rather complex, another way to think about it might be to set yourself a problem for example If the total cost of a project exceeds £3m then I should abandon it otherwise I will go ahead.  How does this look when written out?   ·          =IF ( A1 > 3000000 , ”Abandon” , ”Proceed”) ·          The =IF tells the programme what function you are using ·          All functions are enclosed in brackets () ·          A1 is whe
Read more

Conditional Formatting

Image
  What is conditional formatting?    In literal terms it is getting the format of a cell to change depending upon the content of the cell linked to a rule of some sort. In layman’s terms or to the person on the street it is a helpful way to point out a specific thing in your table of information.    Examples might include entries on an arrivals board at an airport that are late in arriving, examination results of the class that are above and below average and the lists of competitors in triathlon by country or team. Wouldn’t it be really handy when scanning those departure boards at the airport if the flights to Newcastle had a black background and white lettering?    You would be able to pick out the flights pretty quickly if that was the case.    Usually red lettering, numbers or background are the only indications we are given about flight status. So why use conditional formatting? Well, speaking from my own experiences, I use conditional formatting as a method of highlighting error
Read more

Power Query: Merge and Append

Image
  Power Query: Merge and Append Discover the efficiencies and benefits of connecting and combining your data sources to drive your governance and performance measurement! Don`t be put off by the terminology, Power Query is simply sorting and organising data that you wish to import into your Excel spreadsheet and in doing so you can save a lot of time and reduce the scope for errors. ; The merge and append bit is talking about taking different data sources and adding them together to form a single source from which you can then do all sorts of interesting things! First of all we need to define what we mean by  merge  and  append . ; To  merge  a data set in this case means to add one data set to another by adding additional fields. For example: If we have a table of data that contains details of employee departments we might want to add it to another data table that contains details of their salaries. ; In effect we will create a single table with more data fields in the form of additio
Read more