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 not only leveraging BI and location intelligence but also spreadsheet automation. Therefore, it is beneficial to learn how to automate Excel spreadsheets to get the most out of your Excel data and Excel tasks.
Spreadsheet automation 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.
5 Excel Tasks You Can Accomplish with Spreadsheet Automation
Here are five powerful tasks you can accomplish with Excel to improve your business intelligence.
Visualize 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.
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.
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:
- apply spatial analysis such as point-on-area overlays
- enrich your data with online services for drive-time areas, routing, geocoding
- perform geometry validation checks
… to name a few.
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.
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.
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.
For more information on working with Excel, see our tutorial series, Getting Started with Excel.
How do you use Excel data for business intelligence?
Tiana WarnerTiana is a Senior Marketing Specialist 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, riding her horse, and exploring nature with her rescue pup, Joey.
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
Thanks for your comment. I’m glad you found it helpful. Yes, you can easily convert XLS to DBF with FME. See: https://www.safe.com/convert/excel/dbf/
Please don’t hesitate to contact our support team if you need assistance with this. They’re always happy to help! https://community.safe.com/s/submit-case
You can also do the same using free open source software like QGIS (I do it every day!)
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.
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.
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!
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.
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.
If you use VLOOKUP to merge tables, you are probably looking for the FeatureMerger of Matcher transformer.
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.
I’m not sure if I fully understand your question, but you can create an FME workspace that uses the AttributeExploder to pull categories out into their own tables, e.g. https://knowledge.safe.com/articles/1315/advanced-manipulation-of-excel-tables-using-attrib.html You can then run the workspace any time your categories change, and get an updated output dataset. You can also use something like the ExpressionEvaluator to perform calculations on any field. If you’d like help setting up a workspace and going through your problem in more detail, our support team is standing by on live chat and can help you out: http://www.safe.com/live-chat/
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.