Matthew C. Anderson

Author Archives: Matthew C. Anderson

I'm an enthusiastic speaker, creator, and problem-solver.

Identify Duplicate Column Names in Dynamics 365 Using Excel

Here’s a little how-to video that helps how to use Excel to solve the otherwise monotonous process of identifying duplicate column headers in Dynamics 365 / Dynamics CRM (or in any table on a web page). This addresses the underlying issue of the Failed to Generate Excel error message that shows up when clicking Export to Excel.

Background: Failed to Generate Excel

Microsoft Dynamics CRM (now Dynamics 365) has a great feature that lets you export to Excel very easily to use either Excel Online or Desktop edition to play with the data.. Unfortunately, it sometimes gives a nondescript error when exporting “Failed to Generate Excel”, which appears if two column titles (fields) in the export have the exact same name.


Seems a bit over-the-top for an issue with duplicate column names, doesn’t it?

This could be an issue if your organization has a custom field with the same name as an out-of-the-box field and both are included in the view.

The Problem:

Manually identifying duplicate column names is an annoying and often slow process unless the columns happen to be in alphabetical order (and they never are). It would be easy to sort in Excel though…

My Solution:

  • Take advantage of the fact that under the pretty interface, the grid is actually just a table
  • Click-and-drag the text to highlight the column headers in the web page.
  • Copy to clipboard, then paste into Excel.
  • Copy and “Paste Special”–making sure to “Transpose” (rotate) data from columns to rows in Excel
  • Sort this list alphabetically and review for duplicates, then remove duplicates from the CRM view

Note, this is something I use a lot for one-off applications–but there are better ways to do this for the same page on a frequent basis.


Evernote – The first freemium service I paid for (and still do)

If you’re anything like me, you process a ton of information throughout the day. I used to find that a lot of that stuff went in one ear and out the other. I was (and am still) pretty good at remembering the important stuff, and frankly some of the not-so-important stuff too, but there’s just way too much out there to remember all of the details after a quick glance. This is when I turn to Evernote.

Evernote is software that helps me capture notes (text, recorded audio, photos/scans, documents, web pages, etc.) and provides a service that lets me access them from any device using a great text search. It’s easy to capture notes and just as easy to find them later.

Every note sits in a “notebook” which is kind of like a simple folder that the notes sit in. Evernote lets you have as many notebooks as you want.

I started using Evernote as a free app back in the late 2000’s, then in 2010 I made the decision to start paying for their premium version to get a few more features. It’s the first time I was wooed by a freemium service and I have been extremely happy with my investment!

Evernote is the first app I install on my mobile devices

That's my actual home screen. Accept no substitutions.

That’s my actual home screen. Accept no substitutions.

Yup–the very first one. Before anything else. Not only that, but on my Android phone I set up their little widget so I can launch the app, capture a note, capture an image, or record some audio each with only a single click.

It’s a tool that frees me from having to carry a bunch of extra stuff around. I don’t have to save some little scrap of paper that I (or that a friend) scribbled an idea on. I don’t have to tear that ad or article out of a magazine. I don’t have to send an email to myself with that web page or quote I want to reference later. Instead I can send it to Evernote (when I’m on my phone or tablet) or use the desktop app or “web clipper” (when I’m in a browser on my laptop).

Evernote did a good complete reworking of their mobile apps a year or two ago which got rid of a lot of the issues that plague older apps.

The app itself is snappy even on older devices. I have an old phone from a few generations ago that I keep around as a media player and note-taking device which still does just fine running Evernote.

One thing that does cause me occasional issues is that, because I have so many notes in there, space can become an issue. While the app is smart enough to only download the contents of my notes when I need them, the amount of storage space used on the device grows as more and more notes are opened. I’ve dealt with this by clearing the downloaded files, but it’s something I’d like to see be a little more user friendly.

I’m a huge user of Microsoft OneNote, and yes I still use Evernote

It feels important to call this out. I’ve been a OneNote user since Office 2003 (embarrassingly long since it wasn’t that great in 2003). I use OneNote nearly every day and yes I use Evernote just as often. They both work well for different purposes, largely because of the way they are architected.

There are plenty of blog posts out there comparing the two services so I won’t bother with that here. Instead, I want to comment on a couple of the reasons that Evernote continues to be worth the investment even when compared to OneNote which wouldn’t have an incremental cost.

  1. Evernote notes are fantastic containers for all sorts of information. Evernote acts as a simple database for me, but it allows for unstructured data to live inside of these notes, as well as a limited amount of structured metadata like the title, dates, URL (source of a clipping) notebook, and tags.
  2. Evernote provides a more consistent experience across the various platforms (desktop, web, mobile). There’s a trade-off that both Microsoft and Evernote make when it comes to how rich the text/photos and complexity of the way they’re laid out in a note. Microsoft provides a richer experience on the desktop, but those don’t translate as well to a mobile device or to a pure web version. Evernote’s approach, while more limited in the layout features, provides a consistency that I really appreciate.

I got started with the free version, and you can too

I wrote a blog post back in 2010 where I talked about how they had a very compelling offering even with the free version. It wasn’t a crippled version of their product–in fact at the time they included just about every feature, but limited the total upload storage space I could consume each month.

The big feature I wanted when I started subscribing to the premium version was to be able to extend the search to return results inside of attachments like PDF files and pictures. So when I wanted to find a PDF file and the only thing I can remember is that Boba Fett was mentioned in it, I can search for his name and it’ll find the note and the PDF.

Since I became a user, they’ve added a ton of features to the premium offering, but the basic service still lets you create, save, and search notes from the web, a mobile device, or your desktop. When I tell friends/co-workers/random people about Evernote, I usually suggest they just try it out since the free version is so representative of what you get with the paid services.

evernoteversions-2016I grabbed a screenshot of their free vs. paid models. They’ve added a middle tier into the mix as well. The most important reasons I see for the paid versions are:

  • Text search inside of images (plus) and PDFs (premium) — like I said before, it’s a big part of what got me to move
  • Offline notebooks on mobile devices (plus or premium) — There are only a few notebooks I keep offline, but but when I do need it it’s invaluable.
  • Sync with more than 2 devices (plus or premium) — I have too many devices.
  • Remove upload limit of 60MB/mo. (1GB/mo plus, 10GB/mo premium)– This was a thing for me back in 2010 (when the free cap was an at-the-time-generous 40MB). 60MB will probably suit you just fine for text notes and the occasional web page or picture, consider that snapping a quick picture will take up 4MB…yeah that cap is just itching to come off

I could go on and on about how I use it…

…but I don’t want to bury my more detailed stories inside of this post (which feels kind of like unpaid advertising, but I really can’t say enough good things about their service).

In short, I’ve been using the premium subscription for over 6 years and I’m as satisfied now as I was when I started. There aren’t many services I’ve been that happy with–especially when it includes desktop apps, a cloud app, mobile apps, all connected with a cloud-based service. Seriously, when I think of how many freemium services have seemed great and then faded with subsequent releases, I’m really glad they’ve been such a solid team.

Do you use Evernote or perhaps know of something else that’s better? (I question whether the latter is possible…but I am curious) Leave a comment and let me know.

It’s a poor craftsman that blames his tools – I both love and hate this phrase…

…and I use it all the time for that exact reason. I’ve been involved with many projects both in my personal and professional life where I have a responsibility to make things work. Whether I’m building a system to collect forecasting data for business planning or making a podcast with my friends, there are a collection of different tools and processes involved. And in both of these situations the right tools can help make a big difference, but they’re also just one piece of the puzzle.

It’s easy to point at tools as the reason for failure

The tools we use, whether we’re talking about a microphone, a notebook, or a software platform, it’s easy to call out how it’s holding us back. I know for me, if I’m trying to do something and it doesn’t go as planned, one of my first reactions is to look outward at those very tools as the resason things didn’t go better.

It’s then very tempting for me to examine all of the reasons I need a better tool. It’s temping to do this as a way to avoid looking inward at how I’m actually asking people to use the tools we have. Are we experts with these tools? Do we really know how/when to use them?

Instead of taking the easy way out–casting blame at the tools–I prefer to pause and understand how we might be able to make better use of the tools. Figure out what went wrong or where the gaps are where these tools don’t seem to be good enough. More often than not I find that there’s some room for improvement of the process.

Then again, sometimes tools really do matter

Tools do matter (this is precicely why I have the love/hate relationship with this phrase). I find that this is especially true when our skill levels are either really low or really high.

When our skill levels are really low, fancy tools can help cover up mistakes. Autotune for a singer is a great example here.  Someone with practically zero skill, experience, or understanding can sound like a reasonably decent singer. There’s an unnatural quality to it, but it has better appeal than their natural singing voice.


When our skill levels are really high, then better tools can offer a greater level of precision, or help out with some of the low-value work to free up more bandwidth to focus on the finer touches that a true craftsman can bring to a job. This is the singer whose talent is showcased through a well-produced recording.

It’s a trap!

The point in all of this is the following: Tools are important, but they’re only one aspect of what we need to be successful. With the autotune example above, this can also be a trap for someone. They can rely on this tool as a crutch.

When starting out with something new, there’s an additional hurdle that we don’t even know which tools are good or bad for solving our problems. This makes matters worse–especially in a group setting where people will express their deep concerns over this or that tool. The fact of the matter is that every tool will have trade-offs. Once a decision is finally made on how to move forward there’s a feeling of finality, when in reality this is only one of many tools that will be used along the way.

Making do when you need to (but aspiring to more)

What I advocate for (which is where I’ve experienced the most success) is identifying a reasonable starting place. Do enough research about the available tools to get an understanding of what some of the appropriate options are. Ask around–there are probably experts with just about every one of the tools solving similar problems to the ones you’re trying to fix.

The next step is to make a decision for the starting point. Which tool can we try out that seems like it might be the one for the job? There is almost certainly NO “perfect” tool with where you’re at in the process. Even if you pick the one that ends up being the perfect thing–it may take a long time to realize it.

The last step here is so important: take time to objectively evaluate the tool. This is different from blaming or praising the tool in the heat of the moment. There will be good jobs and bad ones using the same set of tools. Taking the time to review the effectiveness of these tools over time enables us to identify when to make a move and to make more informed decisions in the future.

Which tools do you use today that you really like? Which ones are you using that you want to upgrade? I want to hear and respond in the comments!

Photo Credits:




It is with great excitement that I set another post to go live on Connecting The Data after nearly two years of posting to my company’s blog. I’ve missed some of the freedom that comes with posting here (variable post length, topic variety, sharing more opinion, format variety to include video & audio, etc.) .

I’m very proud of the recognition and reach I’ve achieved with my blog posts on the Hitachi Solutions blog. It has been a great opportunity to work with that larger audience and do my part to help showcase some of the talent and leadership we have in our organization. We went from having only a handful of authors who each needed to post 1-3 blogs per month, to a stable of over 25 bloggers. This is great growth for Hitachi, but it’s meant that my ability to share my thoughts on interesting topics needed a fresh outlet. I knew there was a reason why I just couldn’t let go of Connecting The Data 🙂

The timing is appropriate as well. The advances by Microsoft in their entire Office 365 suite and the pivot to leverage an open data service for their Dynamics business applications, expansion into machine learning and predictive analytics, visualizations with Power BI, and the improved capability for true productivity applications are just some of the developments that speak to the meaning of this blog’s namesake. MS has really adopted the notion of “connecting the data”.

I’m also excited to expand even further some of the content. I’ve found that my most-read posts involve me sharing creative problem solving using tools and technologies that are approachable to an “average” person without a computer science degree. This means some more fun examples that go beyond using just the core office suite (Scrivener, Audacity, and just to name a few). I’m also going to broaden my horizons and use real world examples that go beyond a business scenario, diving into how I use these technologies as part of getting stuff done outside of the office as well.

I’m looking forward to connecting once again! 🙂


5 Tips for using Excel Templates in Dynamics 365

Now that the Excel templates for Dynamics 365 have been released and in the wild for several months, it seems an appropriate time to share some tips for getting the most out of them (even if you’re just getting started).

The following items are focused on general user experience rather than advanced Excel functionality. (Crawl. Walk. Run. We’re crawling :))

1) Make sure you don’t have duplicate column names in your view

When first testing the capabilities of Excel template functionality I ran into a generic “Failed to Generate Excel” error message when downloading the baseline excel file. After some searching I discovered that the error was thrown because my view had two columns with the same name listed. With a little re-work of the view I was able to consolidate the columns listed and successfully download the file.

The easiest way to avoid this is to be intentional with which fields are added to the excel template download. I had checked the box to select “all” fields for the opportunity entity, and there were some duplicates four out of the box vs. custom fields with the same name. In a real world use case it is important to make sure that the proper field has been selected. So I recommend taking the extra time to validate specifically which fields have been added to the view (and understand why each field is being included).

2) Set PivotTables to automatically refresh data

By default, PivotTables are not set up to automatically refresh based on the new data in the CRM Data table, forcing you to right click on each pivot table and click refresh after opening the template. This hassle is completely avoidable and very simple to fix when setting up the excel template.

xl pivot options


  • Right click on the PivotTable->PivotTable Options
  • Select the Data tab
  • Check refresh data when opening file

BONUS TIP: Uncheck the save data option to make sure the exported CRM data is not stored with the template you’re building.

3) Use Multiple sheets in the workbook

In the examples provided by Microsoft, they have inserted their charts and tables above the data set (by adding blank rows above the CRM data table). This approach is valid and is certainly a simple way to get started, however, using additional worksheets inside of the excel workbook allows greater control over formatting and can help keep the report looking nice and clean. Using this multi sheet approach will also save you from headaches when adding in pivot tables as the number of rows and columns will be variable in many common use cases.

Doing this is simple. At the bottom of the excel window find the list of tabs (those are the sheets) and click the plus arrow to add a new one. As a best practice, remember to give good names to each sheet.

xl add tab

BONUS TIP: You CAN rename the tab that has the dynamic data table from CRM. Doing this doesn’t cause problems when people use the templates.

4) Choose the default Sheet a user will open to

This tip is all about the user experience (even if you’re the only person who will use it :)). If you have multiple sheets there’s probably one that makes sense to have open first. There’s a very simple and low-tech way to do this:

When you save the final excel template for uploading, make sure the “landing page” worksheet is active-at-that-exact-moment-of-the-final-clicking-of-the-save-button. This worksheet will now be the one that will be active when a user opens it in Excel (via download or in Excel Online). In this way you can guide their experience to the appropriate sheet without forcing them to click another tab.

5) For Excel Dashboards, consider hiding the “data” worksheet

Sometimes it’s important to see all the details of the records behind your charts graphs and analysis, but other times all you need is the summary. You can help further improve the user experience by hiding the worksheet with the CRM data table.

XL Hide Tab

Doing so will not cause issues when using the final uploaded template. Additionally, for users who do want to see this data they could still unhide that tab if they’re feeling curious.

Final Thoughts

The utility of the Excel Templates functionality opens doors to many quick-wins in CRM 2016 (online, cloud-hosted, or internally-hosted). These tips are a few of the basics to keep in mind when getting started, but we’ve barely scratched the surface with what we can do.

Matthew’s post originally appeared on the Hitachi Solutions blog.

Read more…

Thanks for stopping by. As of January 2015, Matthew’s CRM-related blogging efforts have been refocused into the Hitachi Solutions blog. Excel-related blogging has been moved to his personal blog. The content on Connecting the Data will remain available in an archive state.

CRM 2015 – Quick Public Service Announcement For Administrators

Microsoft has released CRM 2015! In related news I thought I was losing my mind while I was setting up a test organization. I couldn’t find the User administration section. It’s been under Settings->Administration for quite a few releases now, but with 2015 it’s no longer there.


Instead, take a look at the Settings->Security section where you will find Users along with some other items that moved: Teams, Security Roles, Business Units, Field Security, and Access Team Templates. Some new features can be found here as well, including Hierarchy Security and Positions. Both of these will come in handy for better accommodating things like selective access for regional or departmental management teams.


You can use the following link to sign up for a CRM 2015 online trial today:

You can download the Microsoft Dynamics CRM Server 2015 files here:

Three ways Microsoft Dynamics CRM 2015 Will Speed Up Quoting

During my time in sales operations, a constant frustration is that quoting can be a real hassle inside of any CRM system. There are some great tools that help with making certain aspects like configuration much easier, but at the end of the day this is an area that can always be improved (both as the person doing the quoting as well as the person managing what can be quoted).

The recent announcement of Microsoft Dynamics CRM 2015, which is scheduled to come out in December 2014, comes with exciting updates to quoting products or services. I’ve submitted a blog post Three Ways Dynamics CRM 2015 Will Speed Up Quoting to the Hitachi Solutions CRM blog.

I recently learned that reposting the same article content to multiple blogs can actually hurt your search engine rankings, so you’ll have to jump to the full article via the link (re-linked here) 🙂

Dynamics CRM 2015 Preview: Sales Process Enhancements

Anyone who has talked to me about the last two releases of Dynamics CRM knows that I am a huge believer in the Sales Process functionality that Microsoft has developed. I think back to my Sales Operations days and can only shake my head at how much screwing around could have been avoided if this toolset was available today (for sales reps and for my team).

The picture doesn't do justice to the concept...

The picture doesn’t do justice to the concept…but you can click to enlarge the image which will help a little bit.

The CRM 2015 release preview brings even more excitement, as there are even more strides in the ability to guide users through a business process flow. In the last release, we were still limited to a very linear process flow. If there were indicators on an Opportunity that certain stages should be skipped or handled in a different order, it took lots of time for a clever BA or some custom development from an IT pro to make that happen. If the business wasn’t willing to invest in that, then the burden fell to the salesperson to “just know” what was relevant (which meant some people filled in everything while others filled out nothing…garbage data).

Soon, a simple configuration will allow conditional branching to stages based on criteria specified by the process. This capability will be baked right into the 2015 process flow designer, To the right I’ve included an image from the preview guide showing this conditional branching, which should layer very nicely with the existing Business Rules to show/hide relevant fields..

What this means is an even more intuitive experience for the business development team. Fewer refreshes and flickers on the page. Ask only for what’s needed…automate the rest. Get the management team the detail they need for forecasting and decisions, without burdening the sales organization with a bunch of busywork. This is useful in a variety of situations, including:

  • Handling variations for different types of sales (inside sales, add-ons, contract renewals, etc.)
  • Time to productivity when onboarding new salespeople
  • Managing sales cycles that vary if they have a short-fuse or time-based incentives

Keep it coming Microsoft Dynamics CRM Team!

Note: In this post I discuss future products and features that have been announced publicly but it is always important to remember that this is subject to change with the final release