It's been another week of intensive Excel projects and once again I used several tricks I've learned over the years.  It occurred to me that they would make a good short blog article.  Here is one of the techniques.  I'll post another blog article soon with more ideas. Working with Dates. I am often helping clients convert data that's been given to them by vendors or trade shows.  Sometimes the dates come over all jammed together without slashes or in text mode.  To

In an interesting dialog last week, KPIs, or key performance indicators, were the topic but it occurred to me during the discussion that it meant different things to the participants on the call.  That inspried me to write this blog article. What do KPI's mean? And what do they mean to you and your organization. Wikipedia says: "A performance indicator or key performance indicator (KPI) is a type of performance measurement. An organization may use KPIs to evaluate its success, or to

Here's another Excel tip. Just helped a client resolve a situation and thought it would make a good Blog article.  This client had links to external references that were not resolving and Excel would immediately crash and close whenever they tried to open the spreadsheet in question.  Needless to say they were freaking out.  Here's what I did to resolve it. 1. If Excel is still open, click on Task Manager and close the application. 2. Restart Excel and go to a new spreadsheet

Today's quick blog article will talk about a favorite formula string I use time and time again.  If you read my blog, you know I use Excel as my tool of choice for data manipulation. My challenge today was to find and match emails in two columns.  What I wanted to do was weed out emails that were duplicated in both columns.   I used a formula and will explain it below. Let's use the following list as my example: Column A Column B Column C This email address is being protected from spambots. You need JavaScript enabled to view it.  

If you have ever worked with drop down lists in Sage ACT, you know that you can import items to populate the list.  The list has to be a text file with the items one per line, enclosed in double quotes. I like to use Excel to key in the data, sort it, remove duplicates, etc.  Then save the file as a text file.  However, it doesn't put in the double quotes. Here's a formula you can insert in a column next to the data.  Assume the text items are in the A Column. =concatenate

Starting with ACT version 2012, you can now create some informative Excel pivot tables using what is called a Star schema.  The data goes after histories and opportunities. Two tables are created.  It’s a bit complicated to set up, but once done you can start to do some sophisticated drill downs in Excel to show history records.  Here’s a screen shot of one pivot table I just created. The one below is using the Opportunities table. Here’s what I was able to produce in

Today's blog is about using SQL commands directly in Excel.  I am going after data in Sage ACT, but you can use the same technique to go after a wide variety of data sources that are SQL based. First off, open Excel.  I am showing you screen shots from Excel 2010.  You want to click on the data tab and pick "from other data sources". After choosing From other data sources choose From Data Connection wizard which brings up the following dialog box.  Choose Other