Microsoft Excel, the industry standard for spreadsheet data, is an increasingly valued skill in the tech world. While many businesses use the application only for basic calculations, graphing, and analysis, others recognize the importance of leveraging BI and location intelligence.

This isn’t one of those life hack lists that’s going to tell you things like “press Ctrl : to insert the current date” (real tip, btw). This is some serious business, like integrating spreadsheets with other data sources, manipulating and analyzing data in ways stretching beyond Excel’s built-in capabilities, and performing tasks that would best be served in a repeated, automated workflow.

Here are five powerful tasks you can accomplish with Excel to improve your business intelligence.

EvangelistNumber1Visualize the spreadsheet as a map

Though Excel data is tabular, it’s common for it to contain location information. By reading the spreadsheet as we would read a spatial dataset, like GML or an Esri Shapefile, we can gain valuable location-based insights and see spatial relationships that would otherwise go undiscovered. A map is worth a thousand spreadsheet rows, my friends.

With FME, tabular data can automatically be made spatial in three ways.

Convert coordinates to geometry

If your spreadsheet data contains X/Y values or Latitude/Longitude, FME automatically converts the rows to geometry. It recognizes common names for geometry columns, like “latitude” and “longitude”, allowing you to instantly visualize a spreadsheet.

Visualizing Excel data on a map

Location information from Excel data automatically visualized on a map.

Geocode addresses

If your data contains addresses, you can use geocoding to automatically spatialize it. A number of geocoding services can be leveraged in an FME workflow, like ArcGIS Online or Google.

Link to existing geometry

Your spreadsheet might have a column that links to existing geometry stored elsewhere—an ‘Asset Code’ field, a unique ID, even a name. Join the datasets to link the tabular data with the spatial data.

Joining Excel and Esri data

In this example, an Excel file was joined with an Esri Shapefile based on a numerical ID column. The output is point geometries with attribute information from the Excel file.

 

EvangelistNumber2 Merge other data sources with Excel workbooks

Most businesses store their data in a variety of formats. You might have information in rich and complex spreadsheets, demographic databases, GIS, CAD drawings, web services, maybe even orthophotos.

FME gives you the ability to integrate your spreadsheet with data from hundreds of other formats. By building a workflow, you can automatically import all your data and merge it however you choose. The ability to query any data before generating reports is a huge added value to decision-making and analysis.

Once your Excel data is in an integrated workflow, the possibilities for data transformation, QA, and analysis are virtually endless. For instance:

… to name a few.

EvangelistNumber3 Generate reports and summaries in high volumes

Create useful summaries and statistics sheets by transposing or pivoting the data. For example, you can design a workflow that filters the data into different sheets based on the value of a column, then generates a summary report with an analysis of the content. That workflow can be run as many times and as often as you need.

When it comes to repetitive tasks, nothing is more satisfying than having it done automatically while you get on with more important things, like playing Minecraft. That’s the nature of FME: create your workflow once, then use it over and over to do your work for you.

For high volumes, you can automatically generate reports on a schedule, or even set up a self-serve download service where anyone can generate a report on demand.

EvangelistNumber4 Manage and analyze data larger than the Microsoft Excel size limits

Huge datasets can result in poor performance or size restrictions in MS Excel. Thankfully, you can still perform all your analysis and data manipulation outside of Excel. FME’s graphical interface makes working with spreadsheet data intuitive.

You can also create subsets of the large spreadsheet by leveraging pivots and statistics. Cleaning up the data or reformatting the values can also have a big improvement on performance—for instance, by splitting up strings or reformatting date fields.

EvangelistNumber5 Load any data into spreadsheet templates

Much like you can integrate a spreadsheet with data from a variety of disparate sources, you can also merge data to create interesting Excel reports. For instance, you could embed a map or raster image from another source directly into the spreadsheet.

FME offers fine-tuned control over the schema, content, and style of your output spreadsheets. Once you define the styling and formatting—perhaps some combination of predefined pivots, charts, graphs, and images—you can automatically load data into that template at any time. With FME Server, you can automatically run the workspace and update your Excel dataset any time new information is received.

*

If you’re interested in learning how to perform these powerful tasks with Excel spreadsheets, be sure to join our webinar next week on data reporting and analysis. We also have a recording of our Excel webinar that walks through the scenarios mentioned above. If you’re new to FME, I encourage you to download a free trial and try out FME’s Excel capabilities.

How do you use Excel data for business intelligence?

About Data Automation Business Intelligence Data Integration Excel Location Intelligence Reporting Spreadsheets Tabular Data

Tiana Warner

Tiana is a product marketing manager at Safe Software. Her background in computer programming and creative hobbies led her to be one of the main producers of creative content for Safe Software. Tiana spends her free time writing fantasy novels and riding her horse, Bailey.

Comments

12 Responses to “5 Excel Tasks You did not Know You Could Do Automatically”

  1. Daniel Brenner says:

    Thank you big time for the tip on how to make MS Excel spreadsheets spatial. That is really huge; am looking forward to trying it.

    However, ArcGIS still relies a lot on tables in dbase IV (dbf) format. Ever since MS Excel 2007 hit, I have NOT been able to save xls files as dbf files. I currently have to bring xls files into MS Access and pray I won’t get an error message of “Field won’t fit in record” so I can export the file into a dbf. Now I hear Excel 2013 won’t even recognize this format ?!?

    Can I save xls files to dbf format in FME?

    PLEASE RESPOND ASAP.

    Is there any way around this

    • Tiana Warner says:

      Hi Daniel,

      Thanks for your comment. I’m glad you found it helpful. Yes, you can easily convert XLS to DBF with FME. See: http://www.safe.com/convert/excel-xls-to-dbf/

      Please don’t hesitate to contact our support team if you need assistance with this. They’re always happy to help! http://fmepedia.safe.com/knowledgeSubmitCase

      Cheers,
      Tiana

    • Piotr says:

      You can also do the same using free open source software like QGIS (I do it every day!)

      • Tiana Warner says:

        Hi Piotr,

        Thanks for the tip. QGIS is a great piece of software for viewing and analyzing a number of GIS formats. I believe a .xlsx file needs to be saved in a delimited format before working with it in QGIS – but from there, importing tabular data for visualization is easy. Great desktop GIS tool.

        Cheers,
        Tiana

        • Piotr says:

          Yep, to CSV, that’s exactly what I’m doing. The other way round is also possible using ODBC from MSSQL, sqlite or just plain DBF.

          • Ramy says:

            Tiana is right on saying saving xls or xlsx it into a delimited format before importing it into any GIS software. The simplest is to save as a text file with tab or comma as separation. QGIS is a free and evoluting software that is used everywhere now!

  2. Narayan says:

    I am new to FME.How can a function like excel vlookup can be implemented in FME? if you can give me a small example it woud be gtreat. Thanks.

    • Tiana Warner says:

      Thanks for your comment. FME has the Tester transformer for this, which can check if an attribute (column) contains the lookup value. For example: If I know the employee ID is 123 and want to get the employee name, I would set a condition in the Tester to check if Employee_ID = 123. Where it passes, I can then do any number of tasks with that row, like write out the value of the Employee_Name attribute. I hope that helps! Please let me know if you have further questions.

      Cheers,
      Tiana

  3. John says:

    Hello, I am looking for a way to set up a template that will pull constantly changing categories on their own worksheets and then calculate the data found in a specific column.

    Any pointers? This would drastically increase the efficiency of this project.

    Thanks!

    John

  4. Thanks a lot for this awesome tips. I was new to FME and after reading this post I tried its trial version, it was excellent. Thanks for sharing this.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts