5 Data Integration Techniques to Improve Your Excel Data

Excel is a staple format that most organizations use and share. We trust Excel with some of our most important data. Yet, managing your data within Excel can quickly become a tangled web of data and tabular inefficiency.

Many organizations are turning to workflow-based environments to help prepare, integrate and automate their Excel data in a fraction of the time.

In this webinar, learn how data integration methods using the FME Platform can help you:
- Integrate Excel data with other formats
- Validate Excel data
- Automate repetitive workflows
- Prepare Excel data for other applications
- Generate reports

With no need for coding or scripting, achieving remarkable results with effective Excel data management has never been easier.

1. 5 Data Integration
Techniques to Improve
Your Excel Data

2. Kailin
Opaleychuk

Technical Support Specialist,
FME Form

Sara
Mak

Technical Support Specialist,
FME Form

3. Welcome to Livestorm.
A few ways to engage with us during the webinar:


Audio issues? Click this for 4 simple
troubleshooting steps.

4. How to download slides
1.Hover over the
slide deck in the
webinar room
2.Click this button

5. Poll:
How frequently do you use
Excel in your daily tasks?

6. Agenda
1Why Excel & FME?
2Excel vs. FME Vocabulary
3Demos: 5 Ways to Improve Your Excel Data
4Resources
5Q&A


Agenda

7. 1
Why Excel &
FME?

8. Prepare, integrate and
automate your Excel data in a
fraction of the time with FME.
Why Excel & FME?

9. Flash
Demo
Split A Spreadsheet
Into Multiple Worksheets
Why Excel & FME?

10. Thanks to the
City of
Vancouver for
the data!

11. Most people are comfortable
in Excel, however, repeatedly
working with multiple large datasets
can be time consuming.










Why Excel & FME?

12. One platform, two technologies
FME Form FME Flow
Data Movement and transformations
(“ETL”) workflows are built here.
Brings life to FME Form workflows
FME Flow Hosted
Safe Software managed FME Flow
fme.safe.com/platform
FME Enterprise Integration Platform
Safe & FME

13. 29+
27K+
128
190
20K+
years of solving data
challenges
FME Community
members
countries with
FME customers
organizations worldwide
global partners with
FME services
30+
29K+
128
140+
25K+
years of solving data
challenges
FME Community
members
countries with
FME customers
organizations worldwide
global partners with
FME services
200K+
users worldwide
Safe & FME

14. Number of supported data types in FME
1995 2000 2005 2010 2015 2020 2023…
10 100 300 500
GIS
CAD
Database
XML
Raster
3D
BIM
Web
Point
Cloud
Cloud
Big
Data
IOT
Gaming
BI
Indoor
Mapping
AR/VR
Generative
AI
Cloud
Native
Tabular
Unrivalled Data Support

15. How does FME Enhance Excel Data?
Reusable

FME helps organizations
improve efficiency by
automating repeatable,
mundane Excel tasks
Visual

FME helps users visually
document each step
taken in the data
transformation process
Shareable

Reduce requests!
FME Server Apps allow
users to easily build
shareable web apps with
no code
Why Excel & FME?
1 2 3

16. Excel vs. FME Vocabulary
Spreadsheet or Workbook Reader or Writer Dataset

17. Excel vs. FME Vocabulary
Worksheet or Named Range Feature Type

18. Excel vs. FME Vocabulary
Column in Worksheet Attribute

19. Excel vs. FME Vocabulary
Row in Worksheet Feature

20. 5 Ways to Improve Your Excel Data
The Basics


Formatting
& Validation
Integrating
Multiple
Datasets


Pivot
Tables &
Reports


Automating
Your
Reports


1 2 3 4 5

21. 2
5 Ways to
Improve Your
Excel Data

22. Demo 1: The
Basics

23. Goal Block Key Result
Restructure and
summarize
incoming
spreadsheets

The Basics
Data exists
within the same
excel file, but
different sheets
Use
transformers to
combine, filter
and sort
A restructured
spreadsheet
summarizing
license
information

24. Demo

25. Demo 1: Summary
1.FeatureJoiner: Merge data with unique identifier
2.TestFilter: Refine data into different feature streams
3.StatisticsCalculator: Summarize data
4.Restructure data: Use a feature type fanout to create multiple sheets

26. Demo 1: Tips
●FeatureJoiner, FeatureMerger,
DatabaseJoiner
○Vlookup in Excel
●Feature Type fanout
○Use an attribute to split data
systematically to different sheets

Data Attribution
The data used here originates from open data made
available by the City of Vancouver

27. Demo 2:
Formatting &
Validating

28. Slide Title
Stylize
spreadsheet and
share to
SharePoint
Online

Goal Block Key
Formatting & Validating
Result
Attributes need
to be validated,
color coded, &
categorized

AttributeValidator
to validate,
ExcelStyler to
style & color code
Comprehensible
spreadsheet
uploaded to
SharePoint
Online

29. Demo

30. Demo 2: Summary
1.AttributeValidator: create validation rules for attributes
2.Hyperlinks: create a hyperlink text attribute
3.Style and emphasize your data by row or cell
4.Write an Excel file and upload to SharePoint Online

31. Demo 2: Tips
●ExcelStyler
○Highlight data
depending on the data
value
●Validation to preserve data
integrity
●Web Connectors
○easily share your data
with your organization
Data Attribution
The data used here originates from open data made
available by the City of Vancouver

32. Demo 3:
Integrating
Multiple
Datasets

33. Goal Block Key Result
Integrate Excel
data with other
datasets
Integrating Multiple Datasets
Data doesn’t
have geometry
to do spatial
analysis with
Create geometry
& attach spatial
information
using other data
as references
More holistic
view of nearby
businesses

34. Demo

35. Demo 3: Summary
1.Extract data from Sharepoint
2.Extract data from websites (URLs)
3.Geocode the data
4.Spatial join to add more detail to the Excel data
5.Nearest neighbour
6.Set an attribute using a conditional expression
7.Write to a database

36. Demo 3: Tips
●FME has great support for
location or spatial data
●Easy to download data
from Sharepoint or other
web sources
●Conditional tests are easy
in FME
Data Attribution
The data used here originates from open data made
available by the City of Vancouver

37. Demo 4:
Pivot Tables &
Reports

38. Slide Title
Leverage an
excel template
file

Goal Block Key
Demo 4: Pivots and More
Result
Current
spreadsheet has
raw data with no
structure

Avoid cutting
and pasting in
large
spreadsheets

A new report
with summarized
statistics and
graphs

39. Demo

40. Demo 4: Summary
1.Leverage your existing knowledge of Excel
a.Charts
b.Pivots
2.Use an Excel template file to configure the output the way you want
3.Update data without having to cut n’ paste

41. Demo 4: Tips
Use the StatisticsCalculator
to summarize mass amounts
of raw data.
Create charts within FME
and Excel


Data Attribution
The data used here originates from open data
made available by the Government of Canada -
Environment and Natural Resources

42. Demo 4: More ideas...
Excel 3D Maps
Images in Excel

43. Demo 5:
Automating
Your
Reporting

44. Slide Title
Create a
self-serve or
automated mini
BI report

Goal Block Key
Automating Your Reporting
Result
Users may not
have the
know-how to run
FME Form

FME Flow’s
automation
capabilities

Mini BI reports in
an instant

45. Demo

46. Demo 5: Summary
1.Automate repetitive tasks
2.Make your Automations easily available to others to use
3.Combine the power of both FME & Excel to get the reports you want

47. Demo 5: Tips
Automations are very
easy
You can use Excel for
simple BI reporting

48. What About Google Sheets?
●Google Sheets Reader & Writer can be installed straight from FME Form
●Fully supported as of FME b21261+
●Web connection required

49. 4
Conclusion

50. 5 Ways to Improve Your Excel Data
The Basics


Formatting
& Validation
Integrating
Multiple
Datasets


Pivot
Tables &
Reports


Automating
Your
Reports


1 2 3 4 5

51. You can achieve amazing
things with Excel.
By using workflow based environments, you can
manage your Excel data in less time and gain
greater results.

52. 8
Resources

53. Resources:
●Tutorial: Getting Started with Excel and FME
●Knowledge Article: Excel & Pivot Tables
●Blog: 5 Excel Tasks You Did Not Know You Could Automate
●Tutorial: Using a Template File when Writing Excel Data

54. Get our Ebook
Spatial Data for the
Enterprise

fme.ly/gzc


Guided learning
experiences at your
fingertips
academy.safe.com


FME Academy
Resources
Check out how-to’s &
demos in the knowledge
base
support.safe.com
Knowledge Base Webinars
Upcoming &
on-demand webinars

safe.com/webinars

55. Check out
our podcasts
on-demand.
featuring special guest
speakers over at EM360

Resources

56. Peak of Data Integration 2025 -
Call for Presentations Now Open
●Abstracts due Nov 29th, 2024
●All levels FME proficiency welcome
●15 & 25 min options (incl. Q&A)
●Special speaker rate for accepted talks
●Registration opens Sept 17th!


peakofdataintegration.com/call-for-presentations

57. 9
Next Steps

58. We’d love to help you get
started.
Get in touch with us at
[email protected]
Experience the
FME Accelerator
Contact Us
A world where data is not just a
commodity but a catalyst for
real change.
fme.safe.com/accelerator



Next Steps

59. Claim Your Community Badge &
Dive into the new Community!
●Get community badges for watching
webinars
●community.safe.com
●Today’s code: NA09A5


Join the Community today!
Next Steps

60. 10
Q&A

61. Thank You
Recap of Next Steps

1Join the FME Community
2Contact us
3Experience the FME Accelerator

Please fill out our
webinar survey