Creating a report from Excel data

Question

Greetings

THE SITUATION

My organisation tests vehicles for exhaust emissions. This entails the collection of a great deal of data.

Because the tests are conducted at various facilities, although the actual collected data is the same everywhere (e.g., temperatures, vehicle mass), the field names are different everywhere, as is the order of the recording.

So we have to record all data in our own data file. Currently all data is typed into an Excel workbook that currently runs to 30 sheets.

From that Excel workbook it is necessary to produce an effectively formatted test report and a certificate. The certificate must be created in MS Word.

At the moment the test reports are created simply by printing the Excel sheets.

THE PROBLEMS

Excel is not meant for producing well-formatted documents. Frequently the formatting is not as intended and much time is spent fiddling to get things to 'look right'. And to create the certificate requires manually copying the relevant fields from the Excel spreadsheet and typing the information into a Word template. The whole process is tedious, time consuming and prone to errors.

THE REQUIREMENT

At a minimum we would like to see an arrangement where all values are input once only. Secondly, we would then like to have a system that takes that data to complete a blank test report form (that may be in Word but does not have to be) and certificate (which we *think* must be in Word, but we are open to other suggestions.

THE SOLUTION

So far there is a lot of 'bottom-up' effort that principally involves trying to do all manner of things with the original Excel workbook. I am suggesting a top-down solution where we look at a coherent arrangement of input data (format to be confirmed) with a means to take that data and input into a test report template and a certificate template. My original research suggests that maybe Visual Studio can produce a coherent solution? I should also add that the engineers travel globally so any solution must be able to be installed on the user's personal laptop. Additionally, nice to have would be if, when engineers returned to base, they could upload their data to a central database for possible future analysis of all data gathered together.

We have a budget for whatever software infrastructure can provide an effective solution for this as the effective solution, if it can be found, is likely to form the basis of our procedures for many years to come.

If anyone can propose a solution, we would be very grateful. Time is of the essence and I am anxious that trying t find the best way by myself is likely to result in a a number of blind-alleys and wasted time and money.

Answer 1

Thanks Doug and Rohn007

OK, I realise that you need to see what kind of data format we have here in order to have the best understanding and make the most effective suggestions.

I will get the samples and post them up here or e-mail as Doug suggested.

Thanks for your very kind offer of help so far.

Answer 2

Another option to consider is create an "online survey":

• All of your separate locations can fill in the same form.
• All of the data will go in to a single online spreadsheet.
• you can link to the spreadsheet to import it to excel on the desktop
• generate your reports on the desktop (ie PivotTable and PivotCharts).

Here are some articles on using online forms to do surveys

@ Plan a surveyhttps://support.office.com/en-us/article/plan-a-survey-215fedf4-0dcc-4454-8a4c-5600c06650bc****SharePoint Online SharePoint Server 2016 SharePoint Server 2013 Enterprise More...

When thinking about sending a survey to users or other people, it's best to plan the kind of questions you want to ask, and what type of answers you're looking for.

@ Surveys in Excel, hosted online

https://support.office.com/en-us/article/Surveys-in-Excel-hosted-online-5fafd054-19f8-474c-97ec-b606fcda0ff9

Surveys let other people fill out your list—like a sign-up sheet or questionnaire—where you can see it all in one place online. In OneDrive and OneDrive for Business, you can create a survey that others fill out just by opening the link you provide. You’ll see everyone’s response compiled in one online spreadsheet.

@Using Microsoft Forms for quick and easy surveys and quizzeshttps://mva.microsoft.com/en-US/training-courses/using-microsoft-forms-for-quick-and-easy-surveys-and-quizzes-17862****In this Microsoft Virtual Academy we will show you how to use Forms to create a survey or quiz; send out the link; and access the responses.

@ What is MS Forms?https://support.office.com/en-us/forms?ui=en-US&rs=en-US&ad=US&fromAR=1

With Microsoft Forms, you can create surveys, quizzes, and polls, and easily see results as they come in. When you create a quiz or form, you can invite others to respond to it using any web browser, even on mobile devices. As results are submitted, you can use built-in analytics to evaluate responses. Form data, such as quiz results, can be easily exported to Excel for additional analysis or grading. Microsoft Forms is not a replacement for Microsoft InfoPath. Learn more in frequently asked questions.

Forms-Create a Data Entry Form in Excel ****https://www.myexcelonline.com/blog/create-data-entry-form-excel/

Whenever I wanted to input data for my Tables in Excel, I would have a long time inputting the records one by one.  I discovered a handy trick in Excel that can turn my Table into a handy Data Entry Form!

Say goodbye to inputting data into this Table row by row by row by row….

I will show you how easy it is to create a Data Entry Form in Excel with the following steps below...

(SCROLL DOWN TO SEE OUR YOUTUBE VIDEO ON HOW TO CREATE A DATA ENTRY FORM IN EXCEL):!

Download Workbook  http://clicks.aweber.com/y/ct/?l=IJaRP&m=3s7T4bcDf4U0qZ7&b=lK4mEpuNXYGw3alCci.W8A

Report- Analyze Survey Data in Excel + Video****Jon Acampora   November 15, 2015

https://www.excelcampus.com/pivot-tables/analyze-survey-data-in-excel/****Learn how to analyze multiple choicesurvey data.  We will use Power Query to transform the data, then analyze the results with Pivot Tables and Charts. A 3-part video series explains all the techniques in detail.Continue reading   

sample datasheet Analyze Multi Choice Survey Data.xlsx (37.3 KB) Excel Campus

Report-Analyzing Data in Excel Online

https://www.youtube.com/watch?v=lWgdVemOQaA

Learn how Excel Online promotes critical thinking and prepares students for the analytical processes they will experience in college and careers.

Report- How to analyze survey data in Excel?

https://www.extendoffice.com/documents/excel/1476-excel-analyze-survey.html

If there is a list of survey data in a worksheet as shown as below, and you need to analyze this survey and generate a survey result report in Excel, how could you do? Now, I talk about the steps about analyzing survey data and generate a result report in Microsoft Excel.

Report-How to Use an Excel Pivot Table for Data Analysishttps://www.makeuseof.com/tag/use-excel-pivot-table-data-analysis/Gavin PhillipsOctober 15, 2014 4 minutes

The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis.

You can use the pivot table as an interactive data summarisation tool to automatically condense large datasets into a separate, concise table. You can use it to create an informative summary of a large dataset or make regional comparisons between brand sales.

(I have more links if you are interested)

Answer 3

To start with, if you have any control over the testing, you really should come up with a common template for everyone to enter data into. That will save you a lot of time fiddling around.

What sort report formatting are you looking to use?  Can you give us an example of the inputs get and the outputs you want.  Remove any sensitive data, and limit the amount of data you provide us with.

Upload Example - Trouble Shooting - Share OneDrive File

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

Often it is faster and easier for everyone if we have a “sample file” to look at, and “play with”.

This next link provides some tips on setting up a sample file and uploading it for us to access:

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156

**************************************

Here are some generic reporting tips to start with:

**Do Your Excel Reports Document Performance? Or Communicate It?** ****http://www.exceluser.com/reports/do-your-excel-reports-document-performance-or-communicate-it.html

In your Excel reports and analyses, there's a huge difference between documenting performance and communicating it.  Do you know that difference?

When you document, you give your readers mere data. When you communicate, you summarize or filter away the nitty-gritty details, so your readers can see patterns in performance more easily.

Here are seven quick ideas to keep in mind when you want your reports to communicate results...

.   1. Trends Matter.

.   2. Pennies Don’t Matter

.   3. Contrasts Matter.

.   4. Patterns Matter.

.   5. Glitz Is Bad.

.   6. Explanations Matter.

.   7. Readability Matters.

**Five Ways to Make Your Excel Reports Incredibly Popular**

http://www.exceluser.com/reports/five-ways-to-make-your-excel-reports-incredibly-popular.htm

Many Excel reports are a waste of time and money. Here's how to make them useful and popular.

Another Excel user said that he occasionally adds a tiny drop of glue between two pages, and then checks the reports in his managers’ offices to see if the pages have been separated…and they rarely are. So managers seldom even look at his reports, much less read them.

.   1  Ask Your Managers What Reports They Want, and Why

.   2  Explain Performance, Don’t Merely Document It

.   3  Include Small Charts with Trends, and Small Tables

.   4  Always Be Testing

.   5  Reduce Your Reporting Times and Error Rates

**Create Variable-Length, Dynamic Reports Linked to Excel Tables******

When you change a criteria value in a cell, Excel can use formulas and conditional formatting to expand or contract your report automatically, without macros. Here's how.

**Quick Reports With Excel Custom Views** **** August 26, 2011

http://blog.contextures.com/archives/2011/08/26/quick-reports-with-excel-custom-views/

In an Excel file, you might need to change the layout, before you print a report. For example,

    in a customer report, the pricing columns are hidden.

    for a supplier report, you filter for a specific product, and hide some columns.

    for your internal reports, all the columns and rows are visible.

To quickly show the different layouts, without any programming, you can create Custom Views, and select one from a drop down list.

*****************************

I strongly believe that you can use "Get & Transform"/PowerQuery to manipulate your data into a common format. That is also known as "Data Cleansing". 

Then you can move the data into a Pivot table to do calculations, summations etc.

You can create multiple pivots from the same base data to show different facets of the info. 

You can add one or more "slicers" to filter one or more pivots (and their associated charts!).

You can convert the pivots into PivotCharts. 

You can then collect one or more slicers and one or more PivotTables and / or PivotCharts into a "Dashboard".

This page has a collection of free webinars and articles to introduce you to Get & Transform > PivotTables > PivotCharts > Dashboards

Wiki: Get Started with Get & Transform > Tables > PivotTables > PivotCharts > Dashboards ...https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620?tm=1537369871055****Like any Office tool, Excel has a lot of "hidden" features.  I've been learning some of them. One of the really good parts of these features is the automation that is built in to them. Some of these features have been around since 2007, but they have been rarely talked about.   I am going to talk about these specific features

Specifically check out 

https://www.myexcelonline.com/free-excel-webinars  

They periodically offer a free seminar on data cleansing that should be just what you are looking for. I suspect they will be offering it soon. You could also try contacting them to find out when they will next offer it.

Answer 4

If you send me a copy of one of the Excel Workbooks and of the report that has been created from it, referencing this thread in the covering email message, I will take a look at it and see if I can suggest a solution.