How to use Excel VLOOKUP

How to use Excel VLOOKUP

VLOOKUP is a very useful tool in Excel but can be difficult to get right. Here’s how to use Excel VLOOKUP, including an example and what to do when it doesn’t work right.

VLOOKUP is a useful tool for data analysis in Excel, especially merging data from multiple sources. It’s a frequent question in job interviews for business analysts and other jobs that handle data.

Read more

Combining cells in Excel

Combining cells in Excel

Combining cells in Excel is something we frequently need to do. And there are multiple ways to do it, depending on what exactly you need to do. One is easier to remember than the others, but let’s step through them.

I find the easiest-to-remember way to combine cells is to use the & operator with the cells you want to combine, along with any additional text. For example, the formula =A1&” “&B1 will combine the cells A1 and B1 with a space in between them.

Read more

How to sort by color in Excel

How to sort by color in Excel

When doing data analysis, frequently you’ll use conditional formatting to highlight certain cells. But then you’ll probably find you still need to group those similar cells afterward. That’s where sorting by color comes in. Here’s how to sort by color in Excel.

You can sort by color in Excel using either the filtering function, or the Sort button on the Data tab of the ribbon. Sorting with the filtering function is faster and easier, but using the sort button lets you set up complex criteria, including multiple levels. This is useful if you want to sort on both cell color and font color.

Read more

Excel row height must be between 0 and 5.68″

Excel row height must be between 0 and 5.68″

Last Monday, Excel greeted me with a new error message on my work machine, which happens to be a Mac. When I imported a CSV file and tried to change the row height to the default 16 points, I got the message that Excel row height must be between 0 and 5.68″.

I’ve been changing the default row height back to 16 for decades so I don’t know why Microsoft changed it. But they didn’t ask me. Complaining about it doesn’t help either. So I set out to find a workaround. While I observed this on Excel for a Mac, I would expect some versions of Excel for Windows will behave the same way as well. My copy of Excel 2013 on my Windows box hasn’t changed, but that’s the most recent version I have.

Read more

How to create a stacked bar chart in Excel

How to create a stacked bar chart in Excel

My boss’ management is clamoring for metrics. They want to know, at a glance, what we’re doing and how far along we are. Sounds like a job for stacked bar charts in Excel to me. So here’s how to create a stacked bar chart in Excel.

Figuring out a way to track our progress was fairly easy. Figuring out how to make Excel display that chart in a meaningful fashion… Well, that took about five hours. I’ll try to make it easier for you than it was for me.
Read more

How to accept all changes in Word

How to accept all changes in Word

Every couple of months or so, we have to collaborate at work on a Microsoft Word document and submit it without all the distracting markup in it. And it seems like it always takes four of us half an hour to re-figure out how to accept all changes in Word and remove the comments. This applies to Word 2007 and all newer versions.

Sometimes I also find the tracked changes and other markup causes weird problems, and the fastest way to make them go away is to get rid of the markup.

So I figured it might help someone. This is something that either takes you 30 seconds or 30 minutes. If you don’t do this every day, it’s likely to take too long. This is for those of you who can’t do it in less than 30 minutes.
Read more

WordPress Appliance - Powered by TurnKey Linux