It’s not uncommon for spreadsheets to proliferate across an organisation. with work becoming more decentralized and teams working remotely, I’m willing to bet there are multiple copies of every spreadsheet hanging over the metaverse. And yet, there is no inventory or repository kept of all spreadsheets in use. Ability to limit access to data, or spreadsheets, within a workbook that contains sensitive information.

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.Imagine a world without spreadsheets; hard as this may be, they are not fit for the purpose you might be using them for. What in an ideal world would make your life easier? A web based tool, a built in communications platform, a central point for everyone to work in, from designers through to marketers, the ability to manage multiple output channels from one point? I know how wierd it sounds to say “I love spreadsheets!”. I mean, you don’t hear people saying things like “I love Word documents!”, or, “I love JPEGS!” do you?! An approval process that releases a financial model, or a spreadsheet of any kind, into ‘production’. This approval process should certify that the workbook has passed an audit and is available for general use. ⚠ RISK 5 — SPAGHETTI JUNCTION 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! I love a good spreadsheet, I genuinely do, I’ve recently gone through a massive house renovation project and this was essential for managing the budget – there was also the huge satisfaction of when things came in under budget, my formula would automatically calculate a positive! (unfortunately this didn’t happen very often)

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 🤨!) 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: 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. What is the business impact of managing a marketing function with the wrong toolset & what would the impact be if you changed things?Enable role profile access to be provisioned per spreadsheet. Access could be edit or read-only. Copy/paste functionality could be a privilege rather than a default permission. 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. 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? 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. I love spreadsheets! I genuinely do. It may possibly be the nerdiest thing I’ve ever admitted to. Maybe it’s the IKEA effect, having invested 20 years honing vlookup skills, formatting pivot tables, and choosing just the right graph to make my financial data mean something to someone, or maybe I just like the feeling of sinking into that comfortable spreadsheet memory foam. Don’t get me wrong, my spreadsheets are far from perfect, but I just can’t walk away.

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. 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 expect that, in many cases, the answer to that is they either don’t plan, or they spend ages writing stuff out on paper. I know there’s more to a spreadsheet than that, pretty charts, macros, the ability to manipulate data (although I do have to admit that pivot tables are beyond me), however, at its core the above definition is pretty accurate. If this is the case then why are so many organisations still using spreadsheets to manage their marketing campaigns?

Power Query: Within the Power Query Editor, there is a “Remove Rows” tool under the “Reduce Rows” section. Within that is an option to “Remove Duplicates”, which will look at your first column and only keep the first instance of any identical cell values.

On April 16, doctoral student Thomas Herndon and professors Michael Ash and Robert Pollin, at the University of Massachusetts Amherst, released the results of their analysis of two 2010 papers by Reinhard and Rogoff. Reinhart and Rogoff’s work showed average real economic growth slows (a 0.1% decline) when a country’s debt rises to more than 90% of gross domestic product (GDP) — and this 90% figure was employed repeatedly in political arguments over high-profile austerity measures. 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?

However, when they obtained the actual spreadsheet used for the original calculations, they identified three errors.