Formatting cells in Excel if they match a value in another column

This content is 1 year old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A few months ago, I wrote about using Excel to sort a table of names that had been created in Word. And last year, I mentioned a dynamic report I’d created with a colleague, stringing together a few formulae, some data validation and some conditionally formatted cells. Well, today, that same colleague came to me and asked to borrow my Excel skills again.

He had a list of potential sales opportunities and wanted to highlight any cells in the column of client names that matched a list of primary accounts on another sheet. It sounded “do-able”, with some conditional formatting and a formula.

I like a challenge – and it’s as close to any development work as I get these days – so I got stuck in.

It seems the function I needed was =MATCH() although now I’m wondering if =VLOOKUP() might have been more appropriate.

The actual formula used in my conditional formatting rule (applied to data in column E) was this:

=MATCH($E2,'Primary Account List'!$A$4:$A$34,0)

Basically, it’s saying, for the value in this cell, have a look at the data in the Primary Account List sheet, cells A4-A34 and if there’s a match, apply the formatting (bold, orange). I did put $E1 in at first, and the lookup was one cell out… (row 1 is the column headers). E2 is the start of my list but the same formula is used in the conditional formatting rule that covers all the cells.

Featured image: author’s own.

Sorting a Word table of names, in Excel

This content is 2 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I’m going to share a secret with you all, dear readers. I really like Microsoft Excel.

There, I’ve said it. Excel may not be the perfect tool in some scenarios (take my example last year, which should almost certainly have used Power BI) but it is the “Swiss Army Knife” of the IT world. End users and IT admins alike have taken the humble spreadsheet programme and twisted it to suit their purposes. I can do so many things with it.

The problem – a table of names in Word

Like a few weeks ago, when I was reviewing a document. The document contained a table with a list of stakeholders. So as not to offend (in a very hierarchical organisation), it had been decided that the names should be in alphabetical order. Except the author had done that by first (given) name, not by last (family/surname) name. Rather than going back and asking them to fix it, I decided I would do so – as it should only take a few minutes.

Word tables are not always that easy to work with, but copying the data into a spreadsheet would let me mangle it, and then paste it back again. Incidentally, Visual Studio Code is also a fantastic text editor and I often use it for complex search and replace operations.

Moving the data to Excel for processing

In this case, I took the two columns from Word (Name, Job Title) and pasted them into Excel. That gives me something like this (massively simplified for the purposes of this blog post – I wouldn’t really go to this much effort for a three-line table! I also made up the names for this example…):

Mark WilsonPrincipal Architect
Brendan ClarkeDirector
Roger QuinnManager

Splitting first and last names

Next, I added a column to the right of Name, and split the Name column, using Excel’s Text to Column wizard and a space as a delimiter. That gave me separate columns for First Name and Last Name.

MarkWilsonPrincipal Architect
BrendanClarkeDirector
RogerQuinnManager

Sorting the data

I then sorted the data on Last Names:

BrendanClarkeDirector
RogerQuinnManager
MarkWilsonPrincipal Architect

Combining first and last names

Next, I used Excel’s Concatenate function to merge the sorted names back into a single field, for example:

=CONCATENATE(A1," ",B1)

The result is something like this:

BrendanClarkeBrendan ClarkeDirector
RogerQuinnRoger QuinnManager
MarkWilsonMark WilsonPrincipal Architect

Moving the processed data back to Word

Finally, I took the processed data and copied/pasted the combined Name and Job Title text back to Word.

NameJob Role
Brendan ClarkeDirector
Roger QuinnManager
Mark WilsonPrincipal Architect

Featured image by PublicDomainPictures from Pixabay.

Bids, tenders, requests for information and word counts…

This content is 7 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I won’t go into the details (internal company stuff that shouldn’t be on a blog) but at the moment I’m working on a lot of bids, tenders, requests for quotations, requests for information, etc., etc.

I’ve done this sort of work before and it’s not a great fit for me but sometimes it has to be done. It’s my turn. But I hadn’t realised until recently why it is that I struggle so much…

Over the years, I’ve learned to deal with ambiguity; I’ve learned how to respond without having all the facts. I can write convincing copy (at least I think I can) and I can usually spell (despite a colleague suggesting yesterday that I should check a dictionary because “siloes” didn’t look right to him and maybe it should be “silo’s” – arghhh!).

It was my wife who pointed out to me that the very same attributes and skills that help me as an architect (general pedantry; taking the time to consider the various consequences of choices made; a desire to put in place controls to get things right and to do them well) hinder me in a high-pressure sales environment where I don’t have time to think and where everything is urgent/important and needs to be done NOW (or very soon after now)…

…and relax. Because it’s Friday night. And, in a short while, I will have a beer, or a glass of wine, in my hand.

Anyway, what is the point of this drivel? The ranting ramblings of an Architect? No. Ah, yes, word counts.

Counting words in a document – or in a cell in a spreadsheet…

Lots of bid responses are limited in the number of words that can be accepted. Often, the tool I’m using is Microsoft Word and it’s pretty easy to show the word count for a document or part of a document. Sometimes though, I’m using a different tool to create a document. Like Microsoft Excel.

I was working on a form of response that lists several skills and requires a response of less than a hundred words for each. Sounds easy? Maybe, but thirty 100-word responses are still 3000 words… and only having 100 words to detail experience can be limiting sometimes.

I needed a method to count the number of words in a cell of the spreadsheet and, as usual, I found the answer online:

=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

Basically, this compares the length of a string with the length of the same string with all the spaces removed and adds 1 (for a single word with no spaces) or returns 0 if there is nothing in the cell (the TRIM function removes any extra spacing). It’s pretty crude but assuming no hyphenated words or solidi (oblique slashes) it will give a good enough count of the number of words in the cell. Definitely a time-saver for me…

Bonus tip

Excel does have a spell checker – it’s just not very obvious. Just press F7 (or go to the Review menu, then choose Spelling). This only works in the desktop client – not Excel Online.

Short takes: Excel tips to display the worksheet name and validate data; editing Microsoft Project files stored on SharePoint; and an XPS to PDF conversion service

This content is 9 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Another collection of mini-posts based on recent IT trials and tribulations…

Excel tips to display the worksheet name in a cell and to validate data

Last week, I was working on an Excel spreadsheet that acts as a plan for a series of tests. Each sheet has the same format, with some conditional formatting and associated logic to total up passes/fails and give a RAG score for the sheet. Those RAG scores are presented in an overview page – and data is copied between cells so that information is only populated once but appears on every sheet.  I’m quite pleased with the result but I did need to work a little on some of the tricks.

Firstly, data validation in lists (for the pass/fail). This is fairly straightforward but I usually forget how to do it so it’s worth reading the TechNet Productivity Hub post on restricting data entry in Excel with lists.

The second trick was to read the name of each worksheet and use that information in a cell (so I could name a worksheet after a set of tests, and see that name displayed as a header on the page too). Here, the SuperUser site came to the rescue and the code I needed in the cell was:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

Incidentally, I also needed to look something up that I’ve blogged about previously: if a cell shows the formula rather than the result, check the formatting is General and not Text.

Editing Microsoft Project files stored on SharePoint

Much as I try, it seems I can’t avoid working with Microsoft Project. Unfortunately, when working directly from SharePoint the files are opened read-only. The answer, it seems, is to work on a synchronised local copy – as described by Victor Butuza on his Microsoft Office SharePoint blog.

XPS to PDF conversion web service

Every now and again, I find myself wanting to create a PDF from an email, just to upload a receipt to Xero (the expenses system I use at work). Unfortunately Xero isn’t happy with XPS files – and Windows 8.1/Outlook don’t create PDFs, but a quick Internet search turned up XPS2PDF, a simple, fast and apparently secure way to convert my files.  There’s an API for those who want to make the conversion programmatically too.

Excel formula for calculating a price, based on a known cost and margin percentage

This content is 9 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A couple of years ago, I blogged about the difference between margin and markup. Since then, there have been a number of occasions when I’ve wanted to know the formula to take two cells in Excel, one with a cost and the other with a margin percentage, and then calculate the price. I’m sure I’ve blogged that too, but I can’t find it now – so here it is (after I worked it out again this afternoon)…

Using the example above, the formula to calculate the price in cell C2, based on cost in A2 and margin in B2 is =A2/(1-B2).

Fighting with formulae in Excel

This content is 10 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Excel. Love it, or hate it, there are few of us in white collar jobs who don’t have to grapple with it from time to time and, earlier this evening, after OneNote had yet again decided that syncing shared notebooks via our ageing SharePoint platform as not going to work, I needed to come up with a new way of storing actions and decisions for the management team I’m one member of.

I would have liked to create a SharePoint task list but I don’t have the necessary permissions, or the inclination to find out who can give me them (and then to justify why I need to do this). Which led me back to the tool upon which so many business processes are built: Excel.

On the way, I grappled with some formulae, so I decided to knock up some notes for the next time this happens – or the next person who needs to do something similar…

Nested IFs, SEARCHes and ISERRORs

My Excel workbook is simple: two tabs (“Decisions” and “Actions”).  The Decisions tab looks after itself: three columns with a date, a decision, and the rationale behind it.  Actions is a little more complex (read over-engineered).  I have date, status, action, owner, target date, and notes. Then I want the status to be auto-populated based on the notes (If the notes say complete, then status is “Completed”; if the notes include the word cancelled then the status is “Cancelled”, otherwise the status is “Open”).

This was the first challenge.  Excel doesn’t have an ELSE in its IF function, but you can nest IFs as follows:

IF(logical_test, [value_if_true], (IF(logical_test, [value_if_true], [value_if_false]))

(or use one of the LOOKUP functions).  I combined this with the SEARCH function to look for instances of the word “Cancelled” in the notes and everything was working well with results returned for a match on “Complete”, or an instance of “Cancelled” but then the catch all value if false always returned a #VALUE! error.

As SEARCH will always return #VALUE! if the search string is not present, I started to look for workarounds.  Some googling told me that I need to test for an error condition with the ISERROR function but that didn’t seem to solve the issue. So I reversed the logic – check for the error first andthen take action accordingly:

=IF(F2="Complete", "Completed", IF(ISERROR(SEARCH("Cancelled",F2))=TRUE,"Open", IF(SEARCH("Cancelled",F2), "Cancelled")))

It may be easier to understand this in some form of pseudocode:

IF cellvalue="Complete"
{
status = "Completed"
}
ELSE
{
IF cellvalue doesn't contain "Cancelled"
{
status = "Open"
}
ELSE
{
IF cellvalue contains "Cancelled"
{
status = "Cancelled"
}
}
}

Conditional formatting based on cell contents

The next thing I wanted to do was to format each item in the action list according to it’s status and/or date. I wanted to colour completed actions grey; mark cancelled actions as grey and strikethrough the font, then add a red/amber/green status on open actions according to the target date. Conditional formatting did the trick here but I wasn’t sure how to highlight a row based on the contents of one cell in the row.

I found the answer on the HowToGeek site and key to this is not worrying that the reference to the cell used to test the logic is a single cell – Excel is smart enough to work out that you need the same column on the next row when evaluating the condition there.

So, I ended up with six rules, three of which use the TODAY function to evaluate whether the task is overdue or not (or due today):

Formula Format
=$B2="Cancelled" AaBbCcYyZz
=$B2="Completed" AaBbCcYyZz
=$E2="" No format set
=$E2<TODAY() AaBbCcYyZz
=$E2=TODAY() AaBbCcYyZz
=$E2>TODAY() AaBbCcYyZz

It may all be a bit OTT, but I for one work in a very visual manner – I need documents to show me something, not just rely on reading the text.  Hopefully this will work out to be a useful tool for us…

Short takes: Android screenshots; LinkedIn invitation preferences; and hiding Excel headings

This content is 10 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Every now and again, in an attempt to close down some “must blog about that” tabs in my browser, I write one of these “short takes” posts… here’s the latest snippets from the world of Mark…

Screen-grabbing on Android

There have been a couple of occasions recently when I’ve wanted to take a screenshot on my Samsung Galaxy S3 Mini… but couldn’t find how to…

Having to Google to work out how to take a screen shot in Android is… a little odd… but there are a couple of methods mentioned in an Android News post (and six more methods here).  And the reason for the most recent screengrab?  This:

Why does LinkedIn sometimes need an email address before you can send an invitation to connect

I’ve noticed a few times recently that I’ve needed to supply someone’s email address (to prove I know them) when connecting on LinkedIn.  Whilst some Internet reports suggest this is because you’ve been marked as having too many “I don’t know this person” reports, it seemed to be inconsistent for me so that’s not the only reason this happens.  It turns out that there’s an option deep within LinkedIn’s preferences to only receive invitations from members who know your email address.  Now that I know about it, I’ve activated it on my profile too…

Displaying/hiding row and column headings in Excel

I received an Excel workbook recently where all of the row and column headings were missing.  Confused? I certainly was (especially as I wanted to add some columns) but it seems it’s a simple configuration option (just maybe not that commonly used?!), at least in Excel 2013 (your mileage may vary with other versions).

Business intelligence required…

This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Up and down the country, businesses are running on Excel, instead of using a proper business intelligence (or even management information) system. The one I look after is no different but, as I pieced together yet another spreadsheet last weekend, I learned a few Excel tips that might be useful to share…

=SUMIF()

I’ve been trying to pull together a resource forecast in order to work out how quickly to grow my team. The approach I look was to list all the projects we have coming through, with headcount requirements split out by grade, then to total each column based on the grade of staff required.

Seems fair enough, but the trick to making this work is reading a cell and then only including its value in the total if a condition is met (e.g. the indicated grade matches the one I’m adding up).

Stack Overflow came to my rescue, describing Excel’s SUMIF() function

In my case, the formula was something like:

=SUMIF(E4:E148,E154,F4:F148)

Where E4:E148 contained the grades of people for each identified project, E154 contained the grade I was looking for (e.g. Exchange Designer) and F4:F148 were the numbers of people needed for each project that month. Repeat for each grade, and then for each month, and a table of resource requirements can be built up…

There may be better ways to do this, but it will save me some time adding up the totals each time I revisit the task list…

More margins…

Of course, knowing how many people I need is one thing – making some crude assumptions about the likely revenue they might attract to see if I’m close to my numbers for the year is the next question I’ll be asked.

Last week, I blogged about the difference between mark-up and margin, and this week I needed to put that into practice.  I found a forum post that explained the formula (sale price = 100/1-margin * original cost), so I put that into practice, multiplying by a day rate, an assumed number of working days in the month and the total of that grade of person:

=(D165*(1/(1-D174)))*D175*F154

Which translates to:

=(dayrate*(1/(1-margin)))*number of days*number of people

Displaying data in 1000s

The last part was displaying data. Some of the revenue numbers I ended up with are big – and I’m only interested in 1000s of pounds, so I needed to adjust the formatting of the results.  The trick here is to use a custom number format on the cell of 0, (zero comma) for thousands (or 0,, can be used for millions). Add a K or an M on the end for units, and a currency symbol up front too. You can also add a decimal point using 0.0, (e.g. £0.0,K for £1500 to be displayed as £1.5K) or, if the numbers get into the millions, then try something like £0,000,K.

Short takes: searching in Outlook; duplexing in Excel; merging in Word; and going wild in Salesforce

This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

This week I’ve mostly been… working in pre-sales. Consequently, this is perhaps not the most exciting blog post I’ve written… but hey, it’s a post and there haven’t been many of them recently!

First up: searching Outlook

Since I changed jobs in April, my email volume has increased by 300x. My mail archive has more messages in it as we approach the end of June than it did for the whole of 2012, and most of them have been sent/received in the last three months.  In short, being able to quickly and accurately search Outlook is important to me.

Microsoft’s website has some good advice for narrowing search criteria for better results in Outlook – for example, if you’re looking for that email from Mark Wilson with the attachment you needed? Try from:"Mark Wilson" hasattachment:yes.

Next: opening two Excel workbooks side by side

If someone sends you a spreadsheet that you need to complete, and there’s information to pull from another spreadsheet, it can be a nuisance to keep switching back and forth between windows inside the application. The answer is to use Task Manager (taskmgr.exe) to open a new copy of Excel so you now have two running processes.  Each one can be used to open a different workbook (e.g. on different monitors) and contents can be copied back and forth.

Then: merging revision comments in Word

Perhaps you work in a team where instead of collaboratively editing one document, people each create their own versions with their own comments? Thankfully, Word 2010 (and probably other versions too) can merge the comments and changes into a single document. That single feature saved me hours this morning…

Finally: wildcards in Salesforce.com reports

My final tip from “Mark’s exciting week in pre-sales” (I jest) was gleaned whilst trying to create a report in Salesforce.com to show my team’s pipeline. I can’t rely on opportunities being correctly tagged, so I needed a report that used searches on a number of fields (and a filter to apply Boolean logic) but was picking up some false positives.  The problem was that one of the search criteria was also a partial match on some other results.  By changing the “contains” criteria from thing to thing*, I got just the results that started with “thing” and not the ones that included “thing” (like “something”).

That explanation is not as clear as I’d like, but I don’t want to spill the beans on some proprietary information – just take a look at the Salesforce.com advice for refining search using wildcards.

Formulas shown in Excel, instead of their results

This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Every month or so, I have to create a report which is extracted from SharePoint to Excel, and then a couple of extra columns are added with information required by a colleague. The additional columns simply present existing information in a different format but, for some reason, after pasting in these formulae (which are just nested IF statements combined with some string handling functions), the formula is shown instead of the calculated result:

=IF(LEFT(L2,1)=”A”,”Application”,(IF(LEFT(L2,1)=”B”,”Business”,(IF(LEFT(L2,1)=”I”,”Infrastructure”,”Error”)))))
=LEFT(L2,1)&”:”&RIGHT(LEFT(L2,SEARCH(“>”,L2,30)-2),LEN(LEFT(L2,SEARCH(“>”,L2,30)-2))-SEARCH(“>”,LEFT(L2,SEARCH(“>”,L2,30)-2)))

Last month, whilst troubleshooting the issue, I stumbled across a great blog post from Excel MVP, Purna Duggirala (aka Chandoo), entitled “Excel Formulas are not working?!? What to do when all you see is the formula, not result“. Today, I needed to find it again, so I thought I’d blog it this time!

Chandoo’s post lists a number of causes for this issue – mine was a simple issue of cell formatting – after pasting my formula into a new column, the cell is formatted as Text.  Switching the cell format to General, and editing the formula (no changes required, just F2 to edit, then Enter to commit), results in a calculation. Even better, Excel 2010 auto-fills and calculates the following rows for me!

The blog at Chandoo.org (tagline, “Become awesome at Excel”!) is packed with Excel advice and is definitely one to remember next time I’m having problems (or just want to do something a bit different in a spreadsheet).