Cryptography, crime fighting, dyscalculia – it’s been another exciting week of Excel revelations in Twitter. But while some have been dragged to the dark side, others see art and beauty in Excel. And who looks at some old code and wonders what idiot wrote it? Nah, that’s never happened to me!

The Bright Side

• Just distilled a day’s worth of work into a 10 second process. I am the emperor of Excel.
• ms excel 2007 - where have you been all my life? more than 3 conditions for conditional formatting? auto-remove dups? auto table formats?!
• just found out that we’re going to get into day 40,000 this monday july 6th. excel has day 0 in 1900. scared?

The Dark Side

• carefully checked excel sheet before replicating it 15 times When I was done I found the error that now has to be fixed 15 times
• Populating another Excel document that quite possibly will never be looked at or referenced in the future.
• Oh god. I am being dragged over to the dark side. An ‘up’grade to Excel 2007 looks inevitable. By the end of the week… Agh.
• Granted, a lot of my hate for Excel has to do with my hate for algebra. I have a long, intimate relationship with dyscalculia.

Data Analysis

• I love how pasting a excel chart into illustrator gives you an editable vector pretty friggin handy!
• My Data Analyst job is slowly being made redundant. Stop making it easy to interpret data. please? http://tiny.cc/bXuhh Long live MS Excel

Anger Management

• Man, I hate Excel. If you select multiple cells, there is no way to de-select just one. You have to start over. Unbelievable.
• http://twitpic.com/8u7ci - According to Microsoft, I created and saved an Excel file 1st January 1601 at 02.00.
• Wow! My just opened up an Excel wizard and then said “Man, this looks complicated.” Even MS Office wizards are tricky.
• http://twitpic.com/8u2ri - MS Excel hidden anger management function “DAVE RAGE”, no idea what it does, afraid to find out!

Classic Pivot Tables

• is amazing herself with what she can do in excel with a pivot table…its a beautiful thing!
• Little things like finally finding the ‘Classic PivotTable layout’ checkbox in Excel 2007 make me REALLY happy.

Grooving

• What happens when a MacBook, an Excel spreadsheet, the music of Sigurrós and a can of Coke Light come together? A kick-ass project plan!
• Geeking out in Excel and grooving to Beck. Sometimes I don’t hate my job.
• spreadsheets in my business, excel on my mind, in the office all day, no time to unwind.

Career Plans

• Opened up Excel to find out I’d lost yesterday’s 90 minutes of work - somehow it hurts more when you know you did the work on a Sunday.
• Just bought this slick wireless 10-key that has integrated Excel shortcuts: http://tinyurl.com/nbxsxg
• My preferred career plan: Speechwriter who fights MS Excel crime by night
• Sometimes I leave an older excel window open on my computer screen so it looks like I am doing work….

Cracking the Code

• this cryptography course is giving me plenty of opportunity to brush up on my Excel skills. Excel can help with most pre-WW2 ciphers.
• MS Excel 2003 still kicks ass: “Excelence” - A demo made in Excel! http://is.gd/1lh2p #demoscene #bp09 (ed. You might want to turn the volume down for this one!)
• is wondering if microsoft looked at all other programming languages and decided to do the OPPOSITE when designing VB/VBA. It’s useless!
• Revised an old VBA program for a new client; what moron coded this? <ahem>

________________

Who’s visiting Excel blogs and websites? Chandoo, at Pointy Haired Dilbert, reported his stats for June 2009, and a few more people chimed in.

The Spreadsheet Page

Daily Dose of Excel

PTS Blog

And here are the stats for my Contextures website (blog not included)

And this blog — I lost a couple of days of stats when I switched blog themes.

________________________

What danger lurks in the evil spreadsheet? Drama and tension in outer space!

No, Excel shouldn’t change your numbers, but it can happen occasionally, as this poor guy discovered.

Numbers in General Format

As the robot in the video mentioned, you might have problems if you try to enter a large number in Excel. For example, I’ve entered a 16-digit credit card number  — 1234567890123456 — in cell A2.

Everything looks fine, until I press the Enter key.

The cell is formatted as General, which will only display 11 numeric characters. Since the credit card number is larger than that, it appears in exponential notation.

Significant Digits in Number Format

To make the credit card number display correctly, I could try Number format, with zero decimals.

That looks better, except that the last digit has changed from a 6 to a 0. Excel only retains 15 significant digits, so it changes our 16th digit to zero.

Large Numbers in Text Format

Since we need to see all 16 digits in the credit card number, we can format the credit card column as Text, and enter the numbers. All 16 digits will be stored, and will display correctly. Or, type an apostrophe before the credit card number, and it will be treated as text.

More Information

• On the Microsoft site, a brief article on the Number of significant digits MS Excel retains
• Chip Pearson’s article on Rounding Errors In Microsoft Excel97
• For smaller numbers that have been formatted as text, you can Convert Text to Numbers

____________

Remember the old days, when only the kids belonged to Facebook, and they used it to plan parties, and post embarrassing photos? Well, now almost everybody has signed up, and the kids have probably moved somewhere else, and they’re hoping that we don’t discover where.

I have a personal account, and also created a page for Contextures. I hadn’t done much with that page (okay – nothing), and it had a couple of fans. (Thank you, to those first two fans.)

Over the weekend, I read that pages with 25+ fans could register for a customized URL. That sounded like a fun challenge, so I created my first ever book giveaway event (you need to log in to Facebook to see that page). The rules were simple — become a fan by Sunday evening, for a chance to win a signed copy of one of my pivot table books (US and Canada mailing addresses only).

Selecting the winner

Since my books are about Excel, it seemed logical that Excel should pick the winning fan. As I explained in the giveaway announcement:

All fan names will be entered in an Excel sheet, and the Rand function will be used to select the winner’s name.

Here’s how I picked the winner:

1. In cells A1:B1, enter the headings, Name and Number, and format those cells as Bold
2. List all the fan names in column A, under the column heading “Name”.
3. In cell B2, type a RAND formula:   =RAND()

   
   

4. Copy the formula down to the last row of data.
5. To change the cells to values, select all the cells with formulas, and copy and paste as values.
6. Select a single cell in column B, and click the Sort Smallest to Largest button on the Ribbon’s Data tab.
7. The name at the top is the lucky winner of a pivot table book!

Excelapalooza!

Well, the giveaway was fun, so I’ll do it again in a couple of weeks. Or maybe we could organize an Excelapalooza, and everybody with an Excel site or blog could collect names and contribute a prize. Imagine the awesomeness!

______________

Excel wonks? I’m not sure what those are, but I hope it’s a good thing! There were a few interesting links in the Excel tweets this week, and some creative similes. One person has been using a workbook for the past 10 years. I’m sure you have a few Excel files that are older than that! Are there some really old ones that you still use regularly?

And those guys are way too late with their idea for a pivot table group in Facebook. But maybe they could organize a Twitter campaign to get Excel fixed. Now, I’m off to work on some Mad Libs.

Excel Wonks

• I know, I know. bacon done… sandwich eaten. Progressing to the Excel spreadsheet and filtering data, hmm.
• Another good site for fellow excel wonks - http://www.contextures.com/tiptech.html
• I make more Excel spreadsheets by 9 AM than most people make all day. The few, the proud, the former-Marines-who-now-work-in-a-cubicle.

Little Shop of Horrors

• This workbook has gone from merely having a size complex to just being stroppy. It’s like a difficult tennager.
• Forced to use Excel today. It’s like going to Steve Martin’s dentist character from Little Shop of Horrors.
• Dealing with Microsoft Excel 2000 is like using flint tools to skin a bison. Frustrating.

Thanks for the Feedback

• Hell is spending 30 minutes and counting on the phone trying to teach my dad how to sort a column in Excel.
• When the Microsoft Office Excel recovery programme also crashes, you know you’re in for a world of pain
• Amount of time spent in Excel ‘08 today: 20 minutes. Approx portion of that time spend sending feedback to MS about this crappy program: 67%
• Outlook is broken and these folks want Microsoft to fix it: http://fixoutlook.org/ How about EXCEL? Fix Excel…

Chart Wizards

• I sworn I’d never be that guy, the guy who sits in a cube all day making Excel pie charts and bar graphs - yet here I sit….
• Really geeking it up today - Interesting way to do Multiple-baseline charts in excel http://peltiertech.com/Excel/Charts/StackedLine.html
• Excel’s Chart Wizard is the Mad Libs of statistical graphics: template-driven and frequently yielding ludicrous results.

A Zillion Years of Excel

• Number crunching for the past zillion years! The guy who invented excel in 1985 is single handedly responsible for birth of mgmt consulting!
• You made me laugh. "Excel obsolete soon?" Spreadsheets jump started the PC revolution. They’re not leaving anytime soon.
• I just started an excel spreadsheet around 10 years ago and have kept using it since.

Dog’s Breakfast

• no need for Illlustrator, Excel is all you need http://bit.ly/No1Jg (via @jzy) Insane YouTube vid, wonder how long it actually took.
• Made an excel spreadsheet for all the medicine my dog has to take twice a day… We’ll see how he likes those pills at 4 am tomorrow…
• Writing data extraction routines to create skills database. As usual data captured in stupid way in spreadsheet. Excel refuge of the sloppy.

A Typical Tuesday

• As one entrepreneur told me, “With a couple of beers and an Excel spreadsheet, you can make a lot of money in no time.” – WSJ
• Just got a job offer from the Scottish Parliament using VB.NET and VBA - dont know which is worse :S
• Afternoon agenda: Clean up this excel grid, go meet Sandra Day O’Connor, finish weekly POS report. You know, just your typical Tuesday.

Meet Girls

• maybe we should start a pivot table group on facebook. great way to meet girls probably huh?
• Don’t trust anyone over thirty. At least when it comes to word or excel.
• please remember that the last guy I went out with won me over with, "What’s your favorite Excel function?"
• Create an interactive whiteboard Jeopardy game easily using Microsoft Excel. http://bit.ly/kD0KU

__________________

Here’s a favourite message from my mailbox this week:

Subject: help me in excel

Dear sir,

I have a problem in excel i requesting to you solve this. I am sending the data of excel sheet pls look in to that
If you have want any information on that please get back to me
I am waiting for your reply

Regards

Anonymous

Those “Dear Sir” emails make me feel like Peppermint Patty. At least the attached Excel file was small, unlike some of the multi-megabyte files I’ve been sent.

Sorry Anonymous, but I can’t help with your Excel problem today. My desk is piled high with work, and I won’t have any extra time to decipher your file.

Where to Get Help

Fortunately, there are places where Anonymous, or you, can get free help with your Excel problems, or ask questions about other Microsoft products.

• You can post questions in the Microsoft newsgroups, which provide free peer-to-peer support for most Microsoft products.
• Nick Hodge hosts the Excel User Group, which has a very helpful forum for Excel questions.
• You could even post a short cry for help in Twitter, and it’s likely that someone will respond.

These are much better options than emailing me, and asking for private help. Why?

• There are people reading those messages 24 hours a day, 7 days a week – you have a much better chance of getting a quick reply.
• Thousands of people are reading the messages, and probably some of them are experts in the area where you need help.
• Responses are usually very quick, and you’ll sometimes get multiple replies, giving you a variety of solutions.
• When you post a question and get a response, it might help someone else who has the same question later. They can find your question and answer by searching in Google.

Good luck, Anonymous! I hope you find someone who can help with that Excel question.

_______________________

“Help!” said the familiar voice, when I picked up the phone at 10 PM.

“I have a list of orders in an Excel sheet. I want to compare it with the list from last week, and delete all the orders that were in the old list.”

It was my daughter, still at the office, trying to get a pile of work done before the looming deadline. I helped her with a COUNTIF formula, and she was able to leave for home a short time later. Phew!

Find Duplicates With COUNTIF

The first step is to check each OrderID in the new list, to see if it’s also in the old list. We’ll use a COUNTIF formula to calculate how many times each OrderID is found in the old list. If the count is zero, we know it’s a new order.

1. Open both workbooks. Here they’re arranged vertically, so both lists are visible.

   

2. In the new workbook, add a column heading, Dups, in cell D1 in this example. This step isn’t required, but keeps things tidier when you try to sort later.
3. To start the formula, in cell D2, type:   =COUNTIF(
4. Next, we’ll tell Excel where to look for the OrderID. In the old list, click on the column heading for column A, where the Order IDs are listed. That adds a reference with the workbook name, sheet name and column.
   =COUNTIF([Orders_Week01.xlsx]Week01!$A:$A
   
   
5. Finally, we’ll tell Excel what we want to look for. Type a comma, then in the new list, click on the OrderID in cell A2.

   

6. To complete the formula, type a closing bracket, then press Enter. Here’s the completed formula.
   =COUNTIF([Orders_Week01.xlsx]Week01!$A:$A,A2)
   
7. Copy the formula down to the last row of data in the new list. There are 1s in some rows and 0s in other rows. We can see that the first three numbers in the new list are also in the old list, and they have been correctly counted as 1. The next three numbers aren’t in the old list, so their count is zero.

   

Delete the Duplicates

Now that the new orders are identified with a zero, we can delete the old orders.

1. Click in the Dups column heading, and press Ctrl+A, to select the entire range.
2. On the Ribbon’s Data tab, click the A-Z button, to sort the list in ascending order.

   

3. The new items (zeros) will sort to the top of the list, with the old items (ones) at the bottom of the list.
4. Select all the rows with old items, right-click on a row button in the selected rows, and click Delete.

   

5. Finally, to clean up the sheet, delete the Dups column.
6. Save a copy of the revised file, send it off to your vendor, and go home! (Well that’s how our scenario ended – you might have to stay at work for a few more hours.)

Counting in Excel

There are more Excel counting tips on the Contextures website. Also, Chip Pearson has formulas and VBA procedures that you can use with Excel lists.

____________________________

Who knew that Dirty Projectors could make music, let alone create lyrics in Excel? This is the kind of startling information that you can learn in Twitter. Other nuggets of knowledge gleaned from this week’s tweets – birthday parties go better with charts, jewelers need Excel, and Excel is psychic. The beer thing, I already knew.

Take That!

• I hate numbers. And Excel. And numbers that should be the same but that aren’t. For no reason. And people who think that I should know why.
• Dear Microsoft Excel: You are not psychic. Please stop pretending that you know what I want to do. Thank you.
• if my excel freezes one more time im going to continue waiting patiently for it to start working again…take that microsoft!

Years Really Fly

• Ah man. I am already creating charts in Excel. My developer life is *over*. I am slowly turning into a manager.
• thinking my self-proclaimed knowledge of excel was pure hubris. I have no idea how to change the range for this damn bar chart.
• I decided to write down my goals for the future. Three years really fly when they’re on Excel charts… College is only twelve rows away!
• You wouldn’t think that there is much to go into a 2 year old’s birthday party, but my trunk is loaded with stuff including and excel chart!

Fun and Games

• only pitchers of cold beer at half off can alleviate the trauma of daily intense use of microsoft excel
• rick connected the mac-mini to our 40″ flat screen tv~ yikes~the possibilities & imagine running the Excel APP on this screen~way cool!
• And that I’d be a lock for the All Star game if it was based on Excel Spreadsheet ability

Deep Thoughts

• I want to make an excel spreadsheet called sior
• why you can scroll down endlessly in MS Excel where u can’t do so in MS Word for instance!!
• My equations are tight, maths more than aight, I rock Excel, like it’s Saturday night. #badrhymes
• how many calories are burned in a staff meeting? Or typing an excel spreadsheet? Not enough, of that I am sure.

School Daze

• Oh man this Computer Science degree is really helping me programmatically color cells in Excel.
• The teacher ask us if we are liking excel. Everybody answer nooooooooo! hahahahahahahaha xD

Strike Awe

• Can’t help but feel anal as I plan out my drop bags in Excel. But: Luck is the residue of design…
• Dirty Projectors strike awe, Excel spreadsheet-derived lyrics and all http://bit.ly/3zqbH #musicmonday
• most will write their autobiography on Word written in standard book form - mine is an Excel spreadsheet workbook.
• Import web analytics data from Google Analytics in to an Excel spreadsheet http://tr.im/oTQi #wa

Excel Skills Are Like Gold

• I was trained in business that if it wasn’t in excel then what good is it!
• Jewelry shoppe needs a silversmith on staff. A silversmith who can build Excel costing models.
• Today’s interview went downhill when the Exec handed me a pad of graph paper and asked me to write code for an abstract MS Excel scenario.

Have you ever had trouble trying to count items based on two criteria? Don’t worry – it’s Underdog to the rescue!

Note: There’s a short sound clip in the video, so turn down your speakers if your co-workers are napping.

Underdog Comes to the Rescue in Excel

Use SUMPRODUCT to Count

As Underdog mentioned, you can use the SUMPRODUCT function to count items using multiple criteria. In the video, the formula was:

=SUMPRODUCT(–(B2:B8="Sun"),–(C2:C8="Yes"))

There are other tips for counting in Excel on my Contextures website.

Also, Bob Phillips has extensive information about SUMPRODUCT on his xlDynamic.com website.

____________

Yes, the weekend is over, but another one is just five days away! To make it easier to keep track of weekends in Excel, you can use conditional formatting to highlight the rows where the date is a Saturday or Sunday.

For example, our Excel file has a list of product sales for the first half of July. We’d like to highlight the dates and sales numbers that fall on a weekend.

Add Conditional Formatting

To highlight the weekend sales:

1. Select all the cells where conditional formatting should be applied, cells H2:J16 in this example. Cell H2 is the active cell in the selection.
2. On the Ribbon, click the Home tab, then click Conditional Formatting.
3. Click New Rule, to open the New Formatting Rule dialog box
4. In the Select a Rule Type list, click Use a formula to determine which cells to format.
5. In the Formula box, enter a formula to check the weekday of the date in row 2, which is the active row on the worksheet:
   =WEEKDAY($H2,2)>5

   

6. Click the Format button, and select a Fill colour, or other formatting options, then click OK.
7. Click OK to close the New Formatting Rule dialog box.

The weekend rows are highlighted in green.

The WEEKDAY Function

The WEEKDAY function checks the date in column H, and returns a weekday number, based on a numbering system (2) that starts with Monday as 1. Saturday is 6 and Sunday is 7, so we want to highlight weekday numbers that are greater than 5.

Instead of that numbering system, we could have entered 1 or 3 as the second argument (return_type) in the WEEKDAY function. If we entered a 1, or omitted the second argument, the numbering would start with Sunday as 1. With a 3, Monday would start as 0, and end with a 6 on Sunday.

I used the 2 because that groups Saturday and Sunday at the end of the numbering, and we can simply test for >5.

More About Conditional Formatting

On the Contextures website there are basic instructions for conditional formatting, and other examples of ways you can use it.

Watch the Video

Here’s a short video that shows the steps in Excel 2007. No animated creatures in this one, but maybe they’ll be back another day.

__________________