Sometimes someone else says it so well, there’s no point in repeating it. I stumbled across this tip in LauraJ’s Weblog the other day. It’s yet another tool to use in our unending quest to split up first and last names (or any text) in an Excel column. Here’s the link: LauraJ’s Weblog
I’d quote her, but she uses pictures – bless her – and those wouldn’t carry over. Take a little side trip over to her site and then come on back for more about Excel. Thanks, LauraJ!
| Posted on 18-07-2009 I’ve posted an Excel Wiki – I call them “faux-wikis” because you need to click contact or add a comment to edit or add to it, rather than editing directly as you would in a true wiki. It’s ready for your input!
So far, it’s connected to a page about reversing last name first lists and putting them into one column, and some posts about viewing Excel on dual monitors, solving the problem of all of your data pasting into a single cell, and counting multiple variables.
And here’s a silly little tip:
Check marks are useful little critters, and you can use them for counting, too. Many fonts use Alt (Option on Mac) V for a check, but many fonts – especially in Windows – don’t. That means you have to choose Insert>Symbol, find the check mark and insert it. Repeat every time you want it, or copy and paste. More trouble than it’s worth!
Easy fix: Insert a column for your check marks. If you have the font Marlett, change highlight the column and change it to that font. The letter “a” is a check in Marlett.
Now, to count:
Select a cell to enter your count in. Change it to a normal font or you’ll be very confused! Enter:
=COUNTIF(C1:C3,”a”)
The C1 and C3 should be changed to the top cell of your check mark column and the bottom cell of that column.
Here’s how you get to that formula if you want to do it from scratch: Under the ∑ symbol on your tool bar, or Insert>Function, choose or search for COUNTIF. Once you’ve selected it, a box will open. Click in the first box then click into your column and drag from the top to the bottom, or select it in your favorite manner. Now click in the second box and type “a.” Hit enter.
That’s it!
| Posted on 14-06-2009 The site has updated with lots of new information. An Adobe Acrobat Faux Wiki has been added with a couple of supporting pages to address some of those nagging PDF issues and open the floor to questions and tips.
There’s a new page on how to get rid of that annoying markup that remains when you print Word documents after tracking changes here.
Ever paste a table into Excel, only to have it all show up in one cell? Here’s a solution that’s just been moved into the Word Faux Wiki.
There’s a page on The Wonder of Snag-it, a big problem solver for admins.
Finally, there’s a new post right below this one on how to count multiple variables in Excel that will eventually become part of an Excel section.
You might notice a few more little ads at the bottom of the pages. I remain committed to keeping it to a minimum, but I could use a little help supporting site, so if you want any of the software or books you see here, click through!
If you want to be notified of the updates on this site, click on RSS or on “RSS” down at the very bottom of the page. Once you’re there, choose “subscribe in mail” near the bottom of the right hand sidebar.
Cheers!
| Posted on 14-06-2009 I don’t use the full range of Excel, and the people who create it don’t think like I do. Those two factors make the directions difficult for me to follow. Halfway through I’m thinking “What are they going on about??” It just isn’t how I would do it. If you have the same problem and have figured out a few tricks, send them in. Here’s one of my own:
Sometimes I need to count more that one variable in a line, but I can never remember the formula. I finally saved a small spreadsheet with the formula in and called it, “How to Count Multiple Variables in Excel.” Imaginative, aren’t I? If you can’t remember it either, use this formula:
=IF(AND(D3=”d”,E3 =”yes”), 1, “0″)
That means if cell D3 consists of the letter “d” and the cell E3 has the word “yes,” count it. If not, enter “0.” The last set of quotations is for the value “if false.” If you leave out the zero and just use:
=IF(AND(D3=”d”,E3 =”yes”), 1, “”)
Excel will leave the cell blank if both variables are not true.
Of course you would use your own cell numbers and entries.
Click into a blank cell at the end of the column you’ve been entering this formula into and choose “Autosum.” You can find this by clicking on ∑ in your toolbar, by choosing “function” from the insert menu (or pane in 2008), or right clicking then choosing “insert function.” If you use one of the latter methods, you’ll then need to choose “sum.” If you click on ∑ and haven’t pulled down to another choice recently, it will default to “sum.” One click totals! Any way you do it, you’ll have the total number of rows that meet your criterion of having both the specified variables. Clever bugger, isn’t it?
Have an Excel question? Send it in here. More about Excel:
Reversing Last Name First Lists into a Single Excel Column
Excel opens multiple spreadsheets within one “shell,” which is sort of frustrating to people who use two monitors – rather defeats the purpose. The workaround is pretty simple. I blushed that it took me so long to figure it out, but you may be just as slow as I am so here it is:
Open Excel. Drag it to your extra monitor. Open it again. Reread what I just said – don’t make a new workbook…open it again. Bingo – one on each monitor. You can freely drag and drop between them.
