Category Archives: Software Tips

Tips on software used by BA’s, or possibly of interest to them.

Extracting Data from a concatenated string in Excel

Excel. The multi-tool in every BA’s tool-belt. Or if it’s not, it should be.

From capturing user stories, calculating cost basis, data analytics, metrics tracking, management dashboards, and it’s default use as the table-creation-tool du-jour; it does it all. It may not be the BEST tool for any of those but it’s the tool you can count on almost everyone in an office environment having and being at least somewhat familiar with.

It’s also the tool that most people I know turn to when you get one or more data dumps of some sort and you need to try and make sense of the information, or just make it more accessible for further analysis.

In this post I am going to discuss a specific problem I encountered with a data extract in the form of an Excel file. That problem was that:

  • Out of about 25 data elements in the file, only about 10 were in distinct columns
  • The rest of the data elements were concatenated together into one string in a single column
  • There were nearly a thousand rows of data and I would be getting a new file every month, meaning a manual solution was not desirable
  • The discreet data values in the string were not always consistently present (e.g. a value might be present in one row but not another)
  • The data values in the strings were not always in the same order

So in this post I am going to go through the logic and eventual Excel functions that were created to solve this problem.  Hopefully you find it useful.

Continue reading

‘You Suck at Excel’ video with Joel Spolsky

If you are a business analyst, you probably use Excel.  And if you use Excel, there is a REALLY good chance that you don’t use most of its capabilities and don’t know all of the ways to use it most efficiently.  I know that is true for me.  🙂

That’s why I want to recommend you watch this video.  It was private session done by Joel Spolsky to employees of Trello, Fog Creek Software, and Stack Exchange.  And if you weren’t aware of it already; besides founding or co-founding all three of those companies, Joel Spolsky used to be a program manager at Microsoft back in the early to mid-1990’s and he worked a lot on Excel.

He moves quick in this video, but there are a ton of good tips and tricks for using Excel the way most BA’s do.  That is, NOT for rigorous data analysis.  🙂

It’s only 54 minutes long and it might worth your time to watch.


Thoughts on Leveraging OneNote Folder Structure for Searchability and Business Analysis

If it’s not obvious from my prior posts on this subject, I’m a fan of Microsoft OneNote.  I think it’s a fantastic tool for business analysts, but it definitely has its quarks and limitations.

This post will explore OneNote’s various search functions and discuss two ways you can structure your OneNote content to take advantage of its strengths while working around its limitations.  Specifically, using one notebook per project or effort; and defining a reusable folder structure that enables you to best leverage OneNote’s searching and tagging functions.

The Mystery of the Many OneNote Search Functions

The reasoning behind both of these recommendations has to do with limitations of the OneNote search and tag functions, and the fact that these functions are spread out across multiple access points within OneNote.  For example, the Search function (via the Search box) will let you limit the search results by the following criteria (in OneNote 2010 and 2013, which are the versions I have access to):

  • This page
  • This section
  • This section group
  • This notebook
  • All Notebooks

But the “Find by Author” search (which is accessed only via special button under the History tab) will only let you limit the search by:

  • This section
  • This section group
  • This notebook
  • All notebooks

Meanwhile, the “Recent Edits” function (also on the History tab) will initially let you narrow your results by all of the following options:

  • Today
  • Since Yesterday
  • Last 7 days
  • Last 14 days
  • Last 30 days
  • Last 3 months
  • Last 6 months
  • All pages sorted by date

And once you have selected a time period, the list of “Recent Edits” can be further narrowed by selecting from among the following options:

  • This section
  • This section group
  • This notebook
  • All notebooks

Lastly, there is the “Find Tags” function (discussed in the “Using OneNote Tags” article on this blog).  In that function, you can choose to limit the results by selecting from all of the following options:

  • This page group
  • This section
  • This section group
  • This notebook
  • All Notebooks
  • Today’s Notes
  • Yesterday’s Notes
  • This week’s Notes
  • Last week’s notes
  • Older notes

So depending on what type of content you are looking for, you may be limited by different search capabilities within OneNote.  And this is on top of fact that OneNote will not let you combine search times (no ‘AND’ in text or tag search) or combine search terms with other parameters (e.g. search for all instances of ‘Marketing’ in notes that have been updated in the last 7 days).

Given this current set of limitations, how can you structure your OneNote content best?
Continue reading

Using OneNote Tags

This is one of a series of blog posts about Microsoft OneNote that I expect to write. To see all the ones I have currently written, click the OneNote tag.


Tags are a feature of Microsoft OneNote that are often overlooked by users, but which can have out-sized benefits for Business Analysts, students, or others who use OneNote as a tool for gathering notes and information from a variety of sources.

So what are tags you ask?  Tags are essentially bits of metadata you can apply to anything inside a OneNote page and which you can then use to find the things you have tagged with a specialized search function.  This includes any sort of text (of any size from a single letter in a sentence to multiple paragraphs), images, drawings, and even embedded files.

Tags can also be configured to add custom styling or icons to the content that has been tagged and you can even assign more than one tag to the same bit of OneNote content.  However, one limitation is that you can’t apply them to the page, folder, or notebook structures themselves in OneNote.  Not sure what that means?  See the image below.


OneNote comes with some tags predefined, and you can use one by selecting the content to be tagged and applying a tag either by selecting it from the tag drop-down list (show below) or by using a tag hot-key combination.


In addition to the tags that come pre-configured with OneNote, you also have the ability to create your own custom tags.  This means you can create custom tags for a wide range of purposes.

But the reason why you apply the tags is that once you have content in OneNote that is assigned one or more tags you can then easily bring up a list of those things by tag, quickly hop from one to another, or even copy all of the items with one or more tags from their current location into a new page.

The rest of the article is made up of two sections where I will show you:

  1. How to customize the tag list, and
  2. How to use the Tag Summary pane for finding and summarizing tag
  3. Some thoughts on how to leverage tags as a BA

Continue reading

Excel Tip – Formula to Convert Name Order in Single Cells

I haven’t posted anything new to the site in a while both because I’ve been busy with other things, and because the main articles I am working on are ones that will probably take me quite a while to research and write up to my satisfaction.  I have several in the works (including a few smaller ones I can turn to if necessary) so hopefully I’ll have at least one new wiki article up in August.

But in order to make sure the site gets at least a bit of new content each month, I want to share a quick software tip in the form of a couple of Excel formula’s that I wrote.

These formulas convert single cells with names in [Last Name], [First Name] order to [First Name] [Last Name] order.  The first version leaves in middle initials and suffixes. The second version trims everything after the first name.

Continue reading

Share Visio diagrams with almost anyone with SVG

If you’re a BA who does most of your diagramming with Visio, you no doubt know that it’s not uncommon to want to share your work with someone who does not have Visio installed on their computer.

Most BA’s I know get around this by embedding the diagram in a Word or PowerPoint file, since almost everyone in a business environment has those applications available to them.  Or possibly even into Excel.  But embedding your diagram into a different Office application has its downsides.  The diagram is often re-sized, is constrained to the ‘page’ size, and often loses resolution or visual quality.

Another alternative many BA’s use is to save the Visio file as a PDF.  And while most people have Adobe Reader or an equivalent installed, this adds bloat to the file can put into a less intuitive interface.

One option I don’t see used often is to save the file from Visio to the SVG (or Scalable Vector Graphic) format.  SVG has the advantage of being designed for images, especially line-based images, and of being natively supported by a wide range of web browsers.  SVG also has the benefit of being a vector image format (as the name states), which means that the diagram can be scaled up or down to whatever size the user prefers without losing ANY resolution or sharpness.

This makes SVG files easy to share with anyone who has a modern browser.  You can also share them on a project website (as an embedded image even), email, or similar location with an expectation that most users will be able to view them in a high-quality way.

If you want to give SVG a try, just pick a diagram in Visio and “Save as …” to the Scalable Vector Format file option.  Double-click the resulting file and it should open in your default browser.

SVG files are supported in the following browsers:

  • IE 9 to 11 (however, IE does not always scale SVG graphics correctly)
  • FireFox versions 31 and 36+
  • Chrome versions 31 and 36+
  • Safari versions 7+ (and IOS Safari 7+)
  • Opera versions 28+

It may not be seem to be immediately worth it to you, but I recommend you give it a try as I have found the SVG versions of Visio diagrams retain their sharpness better, scale well, and to be more user friendly than converting to PDF, Word, or PowerPoint format.  If you don’t see the same results, than nothing is lost except a few minutes of your time.  If you do like the results, then maybe you have a new option for sharing Visio diagrams in an easier way.

If you have other suggestions for sharing diagrams to stakeholders who may not have Visio, please add them to the comments.

Using MS OneNote for Meeting Notes

This is one of a series of blog posts about Microsoft OneNote that I expect to write.  To see all the ones I have currently written, click the OneNote tag.


As you may have figured out by now, I’m a big fan of Microsoft OneNote.  I find it to be one of the single most useful tools I have as a Business Analyst.  And I don’t know about you, but as a BA I take part in a LOT of meetings.  Project status meetings, elicitation sessions, stakeholder meetings, departmental meetings, and so many more.  And at a lot of those, I either want to or have to take notes.  So here are three quick tips one using OneNote to make your meetings notes (and maybe a few other things) a little bit easier.

Continue reading

MS OneNote 2013 – Free version enhanced

NOTE:  I’m going to start posting some blog entries with tips for software I find useful as a BA. They will likely focus on the Microsoft Office suite applications and free / open source applications. This is the first.

I’m a big fan of Microsoft OneNote.  I think it’s a tool I think that every BA that works in a MS Windows and MS Office-focused environment should be using.  It’s something I also leverage a lot at home for personal use.  Unfortunately, not everyone has Microsoft Office on their home computer.  But did you know that you can (legally) get a version of OneNote for free?  On almost any hardware (including Mac)?

Continue reading