II. Then, upload your spreadsheets and choose your join type. I typically choose Full Join (which Power Query will automatically choose for you), but you can play around and look at which preview fits your needs best. The definition of merging spreadsheets is right in its name — we want to combine two or more spreadsheets into a single spreadsheet. This is a really essential skill to learn for any novice Excel users out there. Interesting… what should I already know? So, spreadsheets — while I 💓 you, there are some areas we need to work on. I went ahead and made a list (I hope you’ll forgive me 🤨!)

The proliferation of spreadsheets across the world of finance is largely due to its flexibility and ease of use. For exactly those reasons, anyone can create a poorly executed, undocumented and visually impressive spreadsheet. Whenever finance think ‘how can I do this without IT or data engineering’, spreadsheets are the go-to tool of choice. The ability to simply, and securely, link workbooks directly to a data source — eliminating the need to replicate data by copy/paste ⚠ RISK 2 — TOO MANY COOKS

Join over 700,000 card lovers and subscribe to Scribbler for the latest on designers, deals and snazzy new products!

I’m not alone either! In 2017, research showed that 71% of organizations are using excel to collect data across business units. The demand for end user computing (EUC) applications has never been higher. Even with the proliferation of platforms across finance, many teams are still reconciling and aggregating data in spreadsheets despite unprecedented investment in IT solutions. The ex-auditor in me shudders when I think of the risks inherent in spreadsheet use across finance. I expect that, in many cases, the answer to that is they either don’t plan, or they spend ages writing stuff out on paper. As a finance leader, I’ve become accustomed to workbooks linking to other workbooks — it’s often the only way to enable multiple workstreams to work simultaneously to create a collated set of financial numbers. What transpires is an untraceable spaghetti junction of formulae and spreadsheets that no one has the time, nor the capacity, to investigate. Tentatively, I put my trust in original preparer in the absence of easy traceability of inputs and formulae.

Andreas Jonsson, cofounder and chief executive of LinkedIn analytics app Shield, says spreadsheets can save you crucial time and money in the early days of a startup. Start by analyzing your processes and identify what your pain points are. Where do you regularly experience frustrations are there any easily identifiable challenges in your marketing process. CFOs must have confidence in their data. They must know the exact data sources that feed into their financial reports — and their providence. The reality is that many finance departments don’t have visibility into data flows across their spreadsheet landscape. The data links between the different spreadsheets across departments, regions, and models are routinely undocumented and so impossible to view and accurately decipher. Dear spreadsheet — if it’s not too much to ask, can you make it easy for me to see where the data is coming from? Ability to assign tasks to team members within a collaboration workbook, and have the spreadsheet do the chasing up!If you’re into coding, working with Excel in your programs can be really fun! The most common way to connect with Excel is using Visual Basic for Applications (VBA), which is what most Microsoft Office programs are programmed with. There are many resources available online but here’s a great starter. If you run into problems, let me just say that you aren’t alone. Here are the ones that I ran into during my own merging experience:

I’ve dealt with marketing managers who run their entire marketing plan from one spreadsheet. This hallowed document takes months to prepare and is access protected to try and manage version control & file corruption; it goes through hundreds of iterations before the marketing director can approve it. The issue with this is not around planning – that’s a perfectly sensible thing to do, the problem is what happens next. A plan is only as good as its execution and using a spreadsheet is not fit for purpose. At this stage what marketers need is marketing resource management (MRM) solution, something that is agile and will allow them to link their plan to actual campaigns and adjust it accordingly when required. Ability to limit access to data, or spreadsheets, within a workbook that contains sensitive information. Built-in workflow approvals that ensure a level of due diligence is followed in the preparation of financial information ⚠ RISK 4 — UNDETECTED ERRORS The Reinhart-Rogoff Facepalm Moment — Harvard’s Carmen Reinhart and Kenneth Rogoff are two of the most respected and influential academic economists active today. Or at least, they were. Hear me out: Spreadsheets can do powerful calculations, they’re flexible, and they’re widely available! Sure, there are plenty of new platforms and apps around wanting to disrupt my spreadsheet habit (Hello, Airtable! I see you Coda 👋)— but I would need to start learning all over again. Spreadsheets are easy. They allow me to play around with numbers, presentation, formulae, graphs. They give me the freedom to prototype a solution without committing to the final output. There are few tools around in the world of finance that can grow with the business, enable finance to adapt to changing business requirements, and empower us to rapidly model business decisions into financial outcomes.However, when they obtained the actual spreadsheet used for the original calculations, they identified three errors. Suddenly, I have error terror! So, what are the most common risks, and what can we do about them? ⚠ RISK 1 — WHERE ARE THE SPREADSHEETS?

I’m going to start with the easiest types of spreadsheet merging and then move onto the harder types of merging. After that, I’ll include some common problems I ran into and some other methods of merging spreadsheets that you could use! Just mentioning Excel, spreadsheets or workbooks is often enough to send most people to sleep. If they do manage to stay awake, they’ll want to change the topic of conversation. Although, if their only experience with spreadsheets is what we were taught at school, I’d be the same so I totally get it. A graphical representation of the interrelationships within a workbook would be really useful. I know I can trace precedents — but it’s a horrible process 🥱 It’s reported that close to 90% of spreadsheets contain errors — given that Microsoft reports over 1 billion O365 users globally — we’re dealing with an erroneous spreadsheet pandemic! Can you think of another system in finance that doesn’t have access controls built in? For good reason, the Sarbanes-Oxley Act of 2002 (SOX) requires that all organisations should behave ethically and limit access to financial data. Data breaches and leaks are not uncommon, and can lead to significant financial loss for a company — recently evidenced by the FinCEN leaks in the US.

Okay, we’re talking basic stuff here. The more advanced user can use spreadsheets to automatically retrieve data from the internet to perform calculations and serious analytical deep dives into the data held. Sounds kind of interesting, how do I do this?