The Secret to Using Microsoft Excel

Yes, there is a secret to using Microsoft Excel.

Welcome to my YouTube Channel:  Dad’s Learning Videos

I’m having fun using YouTube and recording some videos.  I think you’ll enjoy this video.  If ever you watch just one video on Microsoft Excel, this is the video you need to watch.  Put on your headphones or turn on your speakers.  Enjoy.

If you’d like more videos, leave a comment.  And thanks for learning The Secret to Microsoft Excel today.

Microsoft Excel’s =IF Formula Saves Time

=IF is a Tremendous Time Saver

If ever you had a doubt whether Microsoft Excel’s =IF formula can save you time, here’s my favorite =IF formula story.

Learn =IF, It Can Save You Hours of Work Each Week

Be patient, read this entire article.  You may save yourself hours of work each week.

The World's Shortest Excel Book

 

(=IF is a featured chapter in The World’s Shortest Excel Book, available for purchase on this website.)

 

One =IF Formula Saved me 6 Weeks of Work

In the 1990′s I worked as an Excel temp for a long distance phone company in downtown Chicago (perhaps they were MCI, I can’t remember).

On my first day on the 6 week job in November, the manager placed a paper report one foot high on my desk and gave me this verbal assignment.

Our company doesn’t use phone lines, we rent them.  You need to go through each line of this report to make sure that each phone company we use hasn’t overcharged us for the use of their phone lines.

Basically, I had to check about three different columns for each line of data (thousands of lines of data or charges equal to a one foot high paper print out).

The manager spent about 30 to 45 minutes explaining the entire problem to me and in my mind my task sure sounded like an =IF problem.  The manager asked if I had any questions.  I said.

Can I have the spreadsheet that generated the report?

The manager asked me why I needed that and I honestly said I thought it would help me do the assignment faster.  I chose not to tell him about my =IF theory.

At about 9:30 AM I took the floppy diskette (they had no network, there was no cloud computing, no jump drives) inserted it into my old personal computer at work, launched Lotus 1-2-3, and then loaded the spreadsheet.  And then I waited about 30 seconds or more for the spreadsheet to open.  I was holding my breath to see if the old computer they gave me would work.

135 Character =IF Formula Saves Six Weeks of Work

I wish I had saved that formula from 20 years ago.  All I remember is that it was 135 characters long.  And I was nervous as heck working on it and here’s why I was nervous.

First Day of my Temp Project

So by 9:30 AM the manager gave me my Excel assignment walked me away and I was all alone surrounded by strangers working in cubicles.  I was supposed to be sifting through my one foot high report looking for overcharges to the company.

I looked through the first line of data trying to understand the project better.  Remember, look at 3 columns in a row, perform some math on those numbers, and make sure that number wasn’t greater than the “correct” estimated charge for the service.

10 AM on the First Day

The first row of data took about 15 minutes to do.  The second row took about 10 minutes.  The third row 5 minutes.  Then five minutes per row each time.  It took about 30 minutes to get accustomed to this tedious task.  It was now about 10 AM.

10:30 AM on the First Day

Than I began building my =IF test.  It had to be perfect.

Do you realize =IF formulas can be used to generate simple English answers?  For example, let’s say you analyze sales numbers to see if a salesmen grossed over $11,000 dollars in a month.  If they sold more than $11,000 in a month they get a “Bonus”.  See below.

TWSEB =IF Formula Example

The =IF problem I was working on as a spreadsheet temp worker in the early 1990′s was much more difficult than the one shown above.

Is it possible nobody at this company realized they could do this entire project with one powerful =IF test?  Will it work?

For the next 90 minutes I worked the problem.  My first =IF worked.  Then I tried it on maybe 20 rows of data and it had problems.  I received ERR messages (formula doesn’t make sense).  I changed the formula and tried the same 20 rows of data.  The formula was working.

I spot checked all 20 rows of data, everything worked.

Noon on the First Day – Lunch

I knew that my 135 character =IF formula had worked on 20 rows of data.  Could it work on thousands of rows of data with one formula.  Could Lotus 1-2-3 and the old computer they gave me be able to handle this =IF test.  (In the early 1990′s, our personal computers were much slower and had memory limits.)

I wanted to take my lunch and think about my =IF test before running it at 1 PM.

1 PM on the First Day

When I came back from lunch it was the moment of truth.

I copied my working formula and wanted to paste it to thousands of rows of data.  It was perhaps 10,000 rows of data.

I was cautious because I knew my computer was slow and Lotus 1-2-3 might not be able to handle my request because of memory problems.

I pasted my formula into 100 cells.  It worked but it took about 10 seconds.

I pasted my formula into 1000 cells and watched as Lotus 1-2-3 flashed its “WAIT” message in one of the corners of the screen.  It took a minute or so for the paste to complete.

I pasted the formula into several thousand cells.  This took several minutes.

I was cautious and just pasted the cell repetitively for just a few thousand rows at a time.  My old personal computer and Lotus 1-2-3 were working.

I checked my data, the formulas were working.  I spent an hour spot checking data to be sure.

2:30 PM on the First Day

Throughout all of this I was saving my file.  I made sure my floppy diskette (or was it the 3 1/2 inch diskette?) had enough room to save the entire file.

I performed my final File – Save and then considered my options.

  1. Give it to the manager and tell him the 6 week project was finished.  I did this at the risk of the manager saying “thank you” and my project would be over.  My 6 week project would be over and I would be paid for one day of work.
  2. Not give it to the manager.  They had asked me to go through the one foot pile of reports and I could do that instead.

3 PM – The Project is Done

At 3 PM I walked to the manager, gave him my diskette, and told him the project was done.

He was astonished.  Luckily he was open minded.

How did you finish the project?

It took me about 45 minutes to prove to the manager that each row of data had been processed correctly.  I explained the =IF test to him but I don’t know if he understood the formula.

His 6 week project full of potential human errors had been done perfectly in one business day.

My Reward?

I was nervous.  The manager could have thanked me and “let me go” instead of keeping me employed.

At about 4 PM he started introducing me to staff and this is what he said.

This is Richard.  He’s a spreadsheet consultant working on various projects for us this holiday season.  If you have any Lotus 1-2-3 problem just ask him for help.

That’s how I spent one November/December during the 1990′s.

Save Time and Learn Microsoft Excel’s =IF Formula

What can you use =IF for?

  1. Examine the monthly budget items for a non-profit to see if they’re spending too much on a line item.
  2. Determine who has worked overtime this week among hundreds of employees.
  3. Determine how many of your patients have just turned 50 years old and need certain checkups.
  4. Estimate timely re-ordering of critical supplies when they drop below a certain supply level.

I could go on with this indefinitely.  You need to understand and use =IF in your spreadsheets.  It will save you time and help you make better decisions for your company or organization.

The World's Shortest Excel Book

Purchase The World’s Shortest Excel Book today, learn how to use =IF, and have some fun.

The World’s Shortest Excel Book costs $19.95 (U.S.) and I guarantee it will make you a better Excel user.  It comes with a 60 money back guarantee.

 

 

Thanks for visiting Your Excel Coach today and learning more about Microsoft Excel’s =IF formula.

 

 

 

Can Microsoft Excel help me get a job?

Learning Excel is what I call a tie-breaker that can absolutely help you get more interviews and possibly help you land a job at a job interview.

But you need to do the work.  When you’re finished reading this blog post, The World’s Shortest Excel Book can help you significantly improve your Excel skills in 2 days.

The World’s Shortest Excel Book

Use Microsoft Excel on your Resume to Get an Interview

Everyone who sees your resume has an opinion about it.  So here’s my opinion.

Add a bullet list of skills near the top of your resume.  Feature Excel in that bullet list.  Human resources recruiters rarely spend much time reading your entire resume (if at all).  But what you feature at the top of your resume in bullet format is seen.

Adding Microsoft Excel to your bullet list of skills on a resume might look like the table shown below.  Only when you do it on your resume make sure each bullet is only one line long.  Build this table somewhere above all of your job history.

  • Medical Claims Billing
  • Advanced Excel Proficiency
  • Customer Relations
  • Knowledge of EMR Systems
  • Quantitative Analysis
  • Problem Resolution
  • Software Adaptable
  • Managerial Reports
  • Team Player

Having Microsoft Excel on your resume doesn’t guarantee a job interview.  But it could help.  Just imagine that the potential employer needs someone with good Excel skills.  Two people apply for the job and they have identical skill sets, well done resumes, but one person lists “advanced Excel proficiency” while the other candidate.  Remember, the employer wants someone with good Excel skills.

Who do you think will get the call for an interview?  That’s right.  The person with Excel skills will get the call for the interview.

Use Your Knowledge of Excel to Get the Job at Your Interview

If you claim you know Microsoft Excel very well and go to a job interview, you better be prepared to demonstrate you know Excel.

  1. Some employers will give you an Excel quiz or test.  Are you ready for that?  You could be if you purchase The World’s Shortest Excel group, join my user’s group, and study Excel.
  2. You are obviously applying for a specific job at that company.  Study the company’s business and recent articles.  When you come to the interview, tell them how your Excel skills and knowledge could be useful in your new job.
  3. Be ready to describe an Excel technique during the interview.  It doesn’t happen often but an interviewer might actually know Excel well.  Come ready to describe a powerful Excel tool like pivot tables, VLOOKUP, or how you can “inherit” someone’s old spreadsheet and understand it in minutes.  Come prepared. to that interview.

Microsoft Excel is a Tie Breaker in Getting Hired

Once again, if you and your identical twin show up for the same job, if the employer needs an Excel person and you know Excel, you win.  Microsoft Excel is a tie breaker skill.  The more tie breaker skills you possess, the better your chance at getting hired.

Of course, if you lie about your knowledge of Microsoft Excel, you’ll end up wasting both your time and the interviewer’s time.  Perhaps you should take my Excel quiz to unofficially assess your Microsoft Excel skills.

Learn Microsoft Excel and Get Hired

If you’re reading this blog post and have read this far, you already have decided that learning Microsoft Excel is good for your career and your chances at a new job.

If you’re committed to excelling with Excel at your next job interview, purchase my ebook The World’s Shortest Excel Book and join my Excel forum to learn Excel.

The World’s Shortest Excel Book

Thanks for visiting Your Excel Coach today.  You are only 2 days away from significantly improving your Excel skills.  With The World’s Shortest Excel Book, I can improve your Excel skills significantly in just 2 days (or your money back).

Google AdWords Campaign Spreadsheet

If your company is big enough, you either outsource your Internet pay per click advertising or you assign it to an employee (and hope that he or she will do an adequate job).

If you own a resume writing consulting firm or you build garages in Chicago, you may be tempted to begin with Google PPC (pay per click) advertising on your own.

Small Firms need a Google AdWords Campaign Spreadsheet

My Google AdWords campaign spreadsheet has two tools built into it.

  1. Google AdWriting Tool.  This tool is incredibly easy to use.  It’s for writing Google AdWords ads.
  2. Google AdWords Profit Loss Estimator.  This tool is more complex.  It’s for estimating the potential for profit or loss before you begin a Google AdWords campaign.

Google AdWriting Tool

My Google AdWriting Tool blog post has a complete write-up on the use and logic behind this tool.  But here’s the short version of why this tool is such a timesaver and fun to use.

When you type in your Google AdWords ads, my Excel spreadsheet tells you if the ad is too long for Google.  It’s a time saver.

If you’re new to Google AdWords and think it’s easy writing those ads without worrying about their length, you’re wrong.  Write the ad on a napkin at Starbuck’s or write them with Microsoft Word, it’s unpleasant.  Counting your letter length for the ad format (25 – 35 – 35) is unpleasant.

My Google AdWords ad writing tool makes the task easier, using Excel (download at the end of this post).

Google AdWords Profit Loss Estimator

This is the hard part, isn’t it?  If you’re an entrepreneur or small business person, you absolutely need a spreadsheet for this task (download at the end of this blog post).  As an online marketer, here’ my simple Google AdWords Profit Loss Estimator spreadsheet.

Google PPC Campaign Estimator

 

  1. Baseprice – I’m selling a product at $20 that I hope people will renew on monthly.  That’s why cell B7 is $20 and C7 is $60.
  2. Cost per Click – Even though Google estimates my CPC at 75 cents, I hope they’re wrong.  If the cost per click is 25 cents, my test campaign has a chance.
  3. Daily Budget – I’m willing to wager $20 a day for a few days to test the campaign.  If it can sell profitably as a test campaign, I’ll advertise as much as I can up to $100 per day.
  4. Conversionrate – Years ago I converted at between 1 and 1.25 %.  It was an ugly little website that had no stickiness.  All it had was a sales page that was done by me.  Now that I know WordPress I’ve built a website that has value where there’s a greater chance a visitor will stick around and join my email opt-in list.  Hope so.  If it can just sell profitably, I’ll get a professional copywriter for my landing page(s).
  5. Dailynet – Green is for money.  Currently I estimate the test campaign will break even.  If it breaks even, I have a chance to alter it and make a profit.

Will it run at a profit?  Column B is a breakeven analysis.  Column C is the blue sky analysis where my wife is shocked with income magically appearing in our checking account via PayPal.

Campaign Spreadsheet Complications

My Google Adwords PPC spreadsheet is for selling an ebook. 

If you’re selling new garages your estimates will be different.  My only cost was Google AdWords.  If you’re a business owner with a team of garage builders sitting idly by, you have different costs to add to your spreadsheet. 

If you’re thinking of outsourcing a Google AdWords campaign to someone then you need to add that cost structure to your spreadsheet.  Google AdWords experts don’t work for free.

Download the Google AdWords Campaign Spreadsheet

My Google AdWords campaign spreadsheet has two tools built into it.

  1. Google AdWriting Tool.
  2. Google AdWords Profit Loss Estimator.

If you’re good with Excel, you can easily build the spreadsheet described in this blog post.  Or, you can download Your Excel Coach’s Google AdWords campaign spreadsheet.

Whatever you choose to do, best wishes on profitably running your Google AdWords campaign.

Thanks for visiting Your Excel Coach today.

 

Google PPC Ad Writing Tool using Microsoft Excel

It’s funny, I’ve never seen a tool on the Internet for writing Google AdWords ads easily without counting the letters.  Maybe my Excel tool for writing AdWords ads is the first.

Microsoft Excel is the Ultimate Utility Knife Software

Everyone knows Excel is great for numbers and spreadsheets.  But it’s so flexible that I found a way to use Excel to help me write ad copy for Google AdWords.

Google AdWords Ads Layout – 25 – 35 – 35

Since you’re reading this you already know that any Google AdWords ad must conform with the following format.

  • First line:  25 letters or less.
  • Second line:  35 letters or less.
  • Third line:  35 letters or less.

Since many of my Excel visitors do not write Google AdWords ads, this is what I’m referring to.  You’ve seen Google ads, you just didn’t know their format.  Here’s an old Google AdWords ad of mine from 6 months ago.

How to Pass the GED
Iowa has a 99.2% GED pass rate.
Pass the GED with my Iowa method.
howtopasstheged.com/

 

From a Google AdWords standpoint, the ad met Google length guidelines because it was in 19-31-33 length format.

  • First line:  19
  • Second line:  31
  • Third line:  33

You might think that writing Google ads with the correct length would be easy.  It’s actually unpleasant.  I once wrote my ads on a piece of paper when I felt inspired.  When that didn’t work out I used Microsoft Word to write my Google AdWords ads.

I had the same problem.  I was so busy counting the letter length of each line, it stopped me from being creative.  My answer?  Use Excel as a writing tool.

Building your Google AdWords ad writing tool in Microsoft Excel

I want to teach you how to do this, it’s useful.  But if you’re not interested in learning Excel, go to the bottom of this blog post to download Your Excel Coach’s PPC ad writing tool.

Here’s a snapshot of my Google PPC ad writing tool.

Richard's PPC Ad Writing Tool - 1

 

There are three conceptual steps to building this tool.

    1. Type – Just type an Excel spreadsheet that copies my work without using any formulas.
    2. =LEN - Use the =len formula function to build your formulas for column B.
    3. =IF – If you haven’t learned how to use the =IF formula function.
Richard’s PPC Ad Writing Tool – 2

First, just build the PPC ad writing tool as shown above using your version of Microsoft Excel.

The =LEN formula counts the letter length of whatever you type in column A.

The =IF formula examines the length number of column B and then decides if column A is “Long” or OK.  If it’s OK, a blank cell is returned.

Test your Excel PPC Ad Writing Tool

If you’ve built your own Excel spreadsheet for writing PPC ads, don’t forget to test it to make sure it works.  Sounds simple, but you’d be surprised how many follow instructions in building an Excel spreadsheet and then forget to test their work.

Download the PPC Ad Writing Tool

As you read previously, my ad writing tool works very nicely for Google AdWords ads.  But anytime you need to writing creative ad copy with length limitations, you can use Your Excel Coach’s PPC ad writing tool.

Thanks for visiting Your Excel Coach today.

 

Top Ten Microsoft Excel Mistakes

Based upon my experience in teaching Microsoft Excel to several thousand adult learners, here are the top ten Microsoft Excel mistakes that I have seen.  They are in exact order that they occurred to me.

My views on the major Excel mistakes may surprise you.

Inheriting Someone’s Excel spreadsheet Without a Review

I call this the spreadsheet “hot potato” game (hot potato is a game played in the U.S. by children).

When someone is promoted, transferred, fired, or moves on to a different company, someone inherits that person’s spreadsheet.  This normally happens in a space of minutes.

Your manager will tell you to speak to the co-worker who is leaving.  You’re supposed to “take over” his/her spreadsheets.  The person who is leaving will email the spreadsheets he uses or just tell you they are located on a certain folder on the network.

If you’re smart you’ll ask him or her to explain the spreadsheet.  90% of the time they’ll say they don’t have time (They are leaving and they really don’t care.)

If someone who is leaving tries to give you an Excel spreadsheet and they don’t have time to discuss it with you, tell your manager.  Inform your manager that you are uncomfortable taking responsibility for a spreadsheet that has not been explained.

Trusting Numbers People Give You for your Spreadsheet

People collect numbers from different sources and often put them into a spreadsheet doing quality control on those numbers.

The computer guy gave you numbers for your spreadsheet from the mainframe computer?  If you’re serious about accuracy you’ll ask the computer guy what kind of quality control is performed on those numbers.  If you’re too embarassed to ask that question, perhaps you should transfer to a department that just uses harmless programs like Microsoft Word or Microsoft PowerPoint.

A spreadsheet with incorrect numbers you accepted from another source can kill your career.  Few people are fired for spelling errors in MS Word or boring MS PowerPoint presentations.

People are fired or let go for embarassing mistakes on spreadsheets.

Not Understanding MDAS – My Dear Aunt Sally

If you don’t know what MDAS means, you need to take my Excel quiz.

Numbers Entered instead of Formulas are a Classic Excel Mistake

I was once preparing to teach an Excel budgeting class to departments at a prestigious Chicago university.  My client asked me to emphasize the importance of “using formulas not numbers”.

This seemed a bit odd.  Why would people enter a number among rows of formulas.  Totally incorrect technique.

The client looked at me and just said.  “Sometimes they just lie a bit.  They use a number when the formula works against them.”

Faking Your Excel Skills is a Deadly Mistake

As I said at the beginning of this blog post, I’m writing down these top ten mistakes as I think of them based on teaching several thousand Excel adult learners.

People lie about their Excel skills all the time.  They lie about their Excel skills to get a job.  They lie about their Excel skills to get a promotion.  And they lie about their Excel skills so they won’t get fired.

Perhaps you don’t think you are faking your Microsoft Excel skills.  But if you tell your manager you know how to build a pivot table when you don’t, you’re in for a bad surprise.  They’re hard.

If you tell your manager you’ve worked on departmental budgets before when you haven’t, expect trouble.

If you acquired a job using Excel and you lied a little bit about your skill level, you need to get serious about learning Excel, quickly.

Thinking Excel is a Good Database is a Bad Mistake

Microsoft Excel is a good data bucket.  You can sort several different ways and even become talented making =IF statements that help you extract useful information from data.

But Excel is not a very good database.  Why not?

  1. Poor reporting.  Microsoft Access can create wonderful queries on huge amounts of data (give me all the patients taking the XYZ drug for high blood pressure if they are over the age of 65).  And with Access, you can make beautiful reports for your data.  Try doing that with Excel.  You can’t.
  2. The One to Many Relationship.  This is hard to explain.  Excel does one to one relationships.  Access can do one to many relationships.  A classic problem would be keeping track of patients and their multiple visits to their doctor.  With Access, you can set up a database that does that superbly and gives you good reports on the data.  Excel would have a hard time keeping track of patients with multiple visits and then allowing you to good reports from that data.

Excel is great for simple databases with one to one relationships.  A list of all your customers, a list of all your patients, a list of your fantasy football team.  If you need excellent reports or one to many relationship tracking, Excel will let you down.

Trying to Learn MS Excel as if its MS PowerPoint is a Mistake

The first Microsoft software you ever learned was probably Microsoft Word.  At a simple level for memos and two page reports, Microsoft Word is easy.  You had no trouble learning it.

Perhaps you learned PowerPoint.  That was relatively easy.  Just cool menu choices for doing cool drawing techniques.  Even people without artistic skills like me can learn PowerPoint and look decent.

Thinking Excel is like learning Word or PowerPoint.

Learning Excel is not like learning MS Word or MS PowerPoint.

I remember a bring young lady I took MS Word and MS PowerPoint classes from me.  She even took my basic Excel courses.  She did splendidly.

One day as she sat in the third row by the door (yes, I still remember where people sat in class) she was taking a Magic Excel class from me where my focus was using formulas.  I asked her a simple Excel formula question that she would have known if she was decent with math in high school.

She froze in the classroom and could not speak.

After watching this for 5 very long seconds I saved her.  I cracked a joke and said, “Of course you know the answer so I’ll explain for the rest of the class.”  I’ll never ask an adult student a formula question without their volunteering.

She was good at MS Word, MS Powerpoint.  She was good at learning software interfaces.  She was horrible at math.  Microsoft Excel is the toughest Microsoft software you’ll probably ever learn, unless you decide to become good with MS Access.

Using Excel Without Understanding Algebra is a Mistake

I can’t be any clearer than that sentence.

The beauty of Excel is in its formulas, not in its interface.  If you want to become good at Excel, you need to purchase a used high school algebra book and study.

Avoiding Excel Formula Functions is a Mistake

This is kind of like my comment on learning algebra.

Last time I checked, there were over 200 Excel formula functions.  You don’t need to learn all 200.  If you know 10 formula functions you’re better than 80% of all Excel users.  If you know more than 20 Excel functions, you’re the Excel expert in your office.

Learning Excel formula functions is not the same thing as re-learning algebra.

Using a sports analogy, Excel formula functions are to algebra what shooting, dribbling, passing, are to basketball.  Make sense?

Submitting Budgets Without Peer Review is a Mistake

I know, I know, you were expecting magical Excel formulas and techniques in this blog post.  But if you’ve read this post this far you’re an avid Excel student.

If you are submitting an Excel budget you made to your manager, showing it to a talented co-worker would be a good idea.

If you’re a manager submitting an Excel budget to another manager, discussing its accuracy with your staff is an excellent idea.

People make mistakes with Excel budgets in government, business, and non-profits all the time.  If you’re the one making the budget mistake, you are having a bad career day.  If you don’t believe that governments make mistakes with Excel spreadsheets and budgets, just read about the Knox County spreadsheet error.

Not Learning from Other People’s Spreadsheets is a Mistake

If you stay with spreadsheets long enough you become a connoisseur or fan of fine spreadsheets.

  1. When you see a beautiful chart made with Excel, you tell yourself, I can do that.
  2. When you see someone demo a pivot table and you finally understand the power of that technique, you’ll figure out how to do that.
  3. When you observe a simple =IF statement and realize its power, you’ll make that one of the first formula functions you learn.

You can also learn from Other People’s Spreadsheets when you see their horrible errors.

  1. Someone sneaks in numbers when they should have formulas.
  2. Bad Excel users have no explanations on their spreadsheets.
  3. Weak Excel users who don’t understand algebra have two cells of formulas when one formula cell  would have worked.

Study OPS, other people’s spreadsheets, and you will be rewarded with a wealth of knowledge.

Top 10 Microsoft Excel Mistakes

As I said, you probably expected some magical information on formulas in this blog post.

But from my perspective, the top ten Microsoft Excel mistakes were listed in this blog post.  Thanks for letting me be your Excel coach.  If you need private one on one training over the Internet, I can do that for you.  Just leave a comment on this blog post.

Knox County, Tennessee – Spreadsheet Error

When your auditors catch a 6 million dollar error in your cash on hand out of $128.9 million, you’re having a bad day.  That’s a 4.65 % error in cash on hand for Knox County, TN.

I am Neutral

I am not from Knoxville, Tennessee and couldn’t tell you whether they are Republican or Democrat.  I haven’t driven through Knox County in decades.  But I do care about inaccurate spreadsheets in government.

What Does the Knox County Error Teach Us?

The Knox County spreadsheet error of $6 million dollars teaches a lot to any government authority who is willing to learn.  Also, the media could learn a few things about reporting.

3rd Party Audits of Government are Essential

Apparently Knox County was required by their own rules/laws to submit some financial papers to KPMB (a Big Four auditor in the U.S.) for audit.

Deadlines for Audits must be Observed

The Knox County staff submitted their financial papers to KPMB for review 4 weeks after the deadline their own Finance Office set.  Late, late, and later still.

Beware the Software Error Story

Initially the problem or error was explained as a software error.  I think people think its OK to claim software errors because they think it means no person committed an error.  If you’re in the computer business as I am, a software error means that various people forgot to check the software correctly.

But as it turns out, whoever claimed the $6 million error was a software error was just guessing.  Guessing about shortfalls in government money isn’t a good idea.

A Knox County accountant forgot to include or “link” one line item from another spreadsheet.  It was a user error, plain and simple.  Even accomplished Excel users can make mistakes.

Don’t Leave Town after Submitting a Million Dollar Error

After the Knox County accountant submitted his financial papers with a $6 million error, Trustee Office personnel left town without investigating the money shortfall.  I guess they had more important things to do.

Fuzzy Reporting Doesn’t Help

In the Internet news reports I read the media didn’t describe what financial documents were submitted for audit to KPMB (the 3rd party auditors).  That would have been useful information.

The Knoxville media could also have asked how long the county trustees knew of the cash on hand disparity.  What decisions were made on the basis of a presumed shortfall of cash?  That would be a good story.

Research the Entire Story

Just do a search on the Internet for Knox County error.  You’ll find various articles.

Don’t forget to read the KPMB email to Knox County warning them of problems.  Here is that email.

Who Cares?

As I said, I’m not from Knoxville, Tennessee.  I also have made an effort to leave out any names of people quoted in the article.  I’m more concerned about how this spreadsheet error was handled.  The Knox County spreadsheet error is instructive to anyone concerned about how their government money is collected, spent, and audited.

How to learn Microsoft Excel

Someone searched Google for the phrase how to learn Microsoft Excel so I thought I would write a few paragraphs on this difficult topic.

Use your strengths to learn Microsoft Excel

When learning Excel, do not be negative in your approach.

If you walk into an Excel class quietly telling yourself…

I hate math.  Learning Excel will be hard because of the math.

…then learning Excel will be difficult, painful, unrewarding.

I have taught at least 2500 people how to use Excel in the classroom.  I have taught everyone from janitors (yes janitors need Excel) to nurses/doctors/staff to chief executives.  And the people I taught were strong at math, software, and management.

If you are good with math, Excel can be learned easily.

This isn’t a newsflash.  Ultimately, Excel is about using formulas to add, subtract, multiply, and divide.

But more importantly, Excel has over 100 functions that are incredible formula tools.  What’s an example of a function?  =pmt is just one function and its used to calculate a mortgage.  Functions are incredibly powerful because they go beyond the limitations of adding, subtracting, multiplying, and dividing.

If you are good with software, Excel can be your friend.

Perhaps you aren’t a math whiz but you are good with software.  You learned Microsoft Word or PowerPoint on your own.  Perhaps you taught yourself how to do stock research on your own or to blog with WordPress.

If you are good with software, with finding software features that others cannot find, Excel can be your friend.  If you’re good with software than you are already good with using help features in learning that software.

Even if you aren’t good with math you can use your software strengths to learn Excel.  Be strong whenever you learn a new skill, especially Excel.

Managers as Excel Learners

I saved this as a special section.  I’ve taught many mangers how to use Excel.  Here’s my advice.

You’re a good manager.  How does a good manager supervise a task that he or she doesn’t know well?  He delegates the task to a capable person and upgrades his knowledge of the task.

Make sense?  Let’s make this practical.

You are responsible for a budget process but don’t know budgeting.

  1. Find a budget mentor who you can trust and get advice.
  2. Find someone strong with Excel in your department who you can trust to work on the details of your budget process.
  3. Quietly, without drawing attention to your budget or Excel weaknesses, learn Excel and the budgeting process.  Take some classes, preferably outside your work environment.  Or get private Excel tutoring (from me of course).

If you are a manager, use your strengths (ability to delegate, managing your employees, ability to learn new things) to learn Excel as its used in your business.

Where’s my specific advice on learning Microsoft Excel?

Honestly?  My best advice is here.

  1. Teach yourself some simple formulas with Excel.
  2. Purchase The World’s Shortest Excel Book from me.  TWSEB 2003 is here for sale.  My TWSEB 2010 will be on sale beginning November 2011.
  3. If you need more help, I can train you remotely over the Internet.

Always learn from strength, never learn from weakness.  Identify your personal strengths and begin learning Microsoft Excel better today.

 

Random Password Generator in Excel

Has your password ever been hacked?  It’s not fun.  Let’s use Excel to solve the problem.

My Twitter Account was Hacked Twice in Two Days

I’m not a password expert but I’ve never had a computer account hacked until this week.

Twice in the past week someone or some program has stolen my Twitter password and used it to spam my followers with bad tweets.  Not fun.

When this first happened I chose a password that I use very rarely.  It took one day for the hacking program to “steal” my password.  So much for security.

Do you want to protect files, accounts, or your flash drive?

If you’re here to randomly generate passwords for your data files and other accounts, keep reading.

If you’re protecting precious data files on a USB flash drive, perhaps you need a password protected USB flash drive.

Generating Random Passwords

The longer your password, the harder it will be for bad guys to decipher.  But if someone has a program trying to steal your password (or malware), you have more problems than just one password violation.

The Best Type of Password is Alphanumeric

The best passwords have numbers, lower class letters, and upper case letters.  This is an example of what a random alphanumeric password would look like:

J29p830F

A Very Good Password is a Long Random Number

A great password has numbers, lower case letters, and upper case letters.  But I believe a pure random number generated by Excel as a password should be sufficient.  If you randomly generate a 4 or 6 or 8 digit number with Excel, the only way somebody can discover that password is with a computer program (or malware tracking your keystrokes).  Is this number random enough for you?

25657332

Yes, remembering your random password will be hard, but that’s the point.  Unless somebody has a computer program that will do hundreds of thousands of permutations, they won’t be able to determine your password.

Generating a Random Password using Excel

Although a random alphanumeric password is the best choice, an easy 2nd choice is a long random number generated with Excel.  This formula works for all versions of Excel that I have seen.

Random Numeric Password in Excel

Password Protecting your Jump Drive or Flash Drive

I use a jump drive or flash drive constantly.  I know, I could use “cloud” computing but I prefer to physically have my data with me.

But what if someone I lose my jump drive?  Or what if someone steals it while it’s plugged into your computer at work?

SecureStix can password protect your jump drive.  If you’re concerned about creating randomly created passwords for your data files, perhaps you need a solution to password protect your USB Flash Drive.

Generating Random Passwords Online

If you don’t want to use Excel for your next password, use this secure password generator to create your next password.  It’s from PC Tools and I believe it’s a trustworthy site.  Just go to secure password generator and request a password.

30 Passwords or More

I have over 30 passwords for 30 different accounts that I use at work, for online marketing, and for my personal accounts.  I keep my passwords in a “secret” file in Microsoft Word and that file itself is password protected.  I never write down my passwords on paper or in a book (like so many people).

Who made this spreadsheet?

The first question you should ask when given a spreadsheet is…

Who made this spreadsheet?

Too bad spreadsheets aren’t like sidewalks

I grew up in and around Skokie, Illinois, USA.  When I was a little kid, we had many empty lots but we did have sidewalks.

The company that laid the concrete for a sidewalk stamped their name neatly on their work.  They were proud of their craft and their sidewalks.

When’s the last time you saw someone put their name proudly on their spreadsheet?  When was the last time you saw a spreadsheet that listed who contributed to a spreadsheet?  Never is the best answer.

Let Excel tell you who made your spreadsheet

If the person who made your spreadsheet was conscientious, he/she will have entered some information onto the spredsheet.  The problem is finding that information.

  1. Look everywhere.  Look at each cell of the spreadsheet.  This is tedious and you’ll soon tire of the process.  But you might get lucky in finding some ownership information on the spreadsheet.
  2. Spreadsheet tabs.  Look at each spreadsheet tab.  Sometimes better users will mark a sheet tab with a title like Information or Spreadsheet Explanation.
  3. Print preview.  Perform a print preview whether you have an old version of Excel or a newer version.  Sometimes the previous user customized page setup for his/her prints to include their name on a printed reported.  You’ll never see this on the spreadsheet itself.
  4. File – Properties.  This feature is identical in both older and newer (2007/2010) versions of Excel.

Properties was designed to establish spreadsheet ownership

  1. Excel 2003, use File – Properties to access the properties for the spreadsheet.
  2. Excel 2007 or Excel 2010 use a different method to access the identical properties information.  Office button, Excel Properties, Advanced.

This is the Excel Properties Panel.  Notice that the Summary tab is active.

Excel Properties

In the above snapshot, you see:

  1. Author:  Richard Kraneis
  2. Company:  TechSpectrum
  3. Comments:  This is a non-profit budget for 2010.

You will normally see Author and Company information entered by Excel (not by the user).  If you see Comments with meaningful information, consider yourself lucky.

Summary:  Who made this spreadsheet?

This article has given you simple advice for finding out who made your spreadsheet.

You will hope that Excel Properties has been filled out with useful information for determining the string of ownership for that spreadsheet.

But if all else fails, just ask people these questions.

Who made this spreadsheet?  OK, it was Smith from Finance.  How did she get all those numbers from the other departments?  Who gave the numbers to Smith?  Or did Smith get any of the spreadsheet numbers from a computer system?  How do we know that computer system is totally accurate?

Those are the types of questions you ask in determining who made your spreadsheet.

Thanks for visiting Your Excel Coach today.  If you’d like to purchase my Excel ebook that’s perfect for people who love ideas and don’t like numbers, visit The World’s Shortest Excel Book today.  It’s for version Excel 2003.  The revised ebook will soon be available for Excel 2010 (today is September 30, 2011).