excel

Fixing the Dryer: How to Tackle a New Project and Succeed

Throughout my life and career, I’ve been tasked with completing projects with neither the knowledge nor the resources needed.  Growing up without a lot of money, I became accustomed to learning how to do things on my own and always looking for practical, inexpensive solutions to everyday problems.  If something at home broke, I’d ask my parents what to do and they’d help me figure out how to fix it.

Recently, our on-its-deathbed dryer finally stopped tumbling.  It had been making sounds which we should have investigated, but alas, sometimes the saying “if it ain’t (completely) broke, don’t fix it.”  We had been expecting to have to replace the dryer sooner than later and I had planned on finding a working, second-hand one at the local Habitat for Humanity store.  We figured we could get one there for less than $50, which is less than what we’d pay to even have someone come take a look at our dryer.  Granted, our dryer was nice, but not worth spending a couple hundred repairing.

Me, being stubborn as I am, decided to see if we could fix it ourselves.  The thing was still heating and partly working, it just wouldn’t tumble.  Maybe it was a broken belt.  We figured the dryer was already broken, the worst we could do was break it a little more then give it away to someone who knew what they were doing.

I found dryer repair videos on YouTube so I learned a few tips on how to do repairs, and firstly, how to open the dang thing.  Once I got the dryer open and we slowly took it apart, we discovered that dryers aren’t so complicated inside.  We found the problems and less than $80 worth of parts later, our dryer is running in better shape than it has in years.  And we feel confident to make repairs on it in the future.

Had we never made an attempt to fix it, we never would have know how capable we were at doing it on our own.

Self-reliance has been one of the most valuable skills I’ve learned and it is applicable to nearly every situation in life.  When I did have to call a professional for help, as annoying as it may have been, I stayed in the room, watching what was being done and asking questions about the job.  If I had to pay someone several hundred dollars to repair my furnace, plumbing or an appliance, I wanted to know what the problem was, what caused it and how it could be fixed.

The same thing goes for complications that arise at work.  I’ve developed a mindset for requests of “tell me what you’d like to accomplish and I will find out if it’s possible.”  So many people are quick to shoot down ideas, which can actually complicate them more because if you only do bits and pieces of a project, you’re extending the time and energy it takes had you evaluated the request as a whole.

For example, a coworker wanted me to put together an order form in Excel that would allow his customers to calculate full container loads of a variety of products that were not only different sizes, but different designs and categories.  Originally, this had been done by a back and forth exchange in which the customer gave an overall request of what they wanted, my coworker would look up dimensions, minimum order quantities and product availability, consult with the customer about increasing or decreasing items to  fit and so on.  It was about a week-long process and very understandable why an automated order form would be highly desirable.  These were huge, once a year orders.

I spent weeks working on the spreadsheet.  There were so many factors to include, minimum order quantities, case packs, availability of design, products that could not fit on certain containers, etc; it was extremely complicated.  I had to generate item numbers, existing as well as contingent on a customer selecting them.  There also had to be a meter to show the container reaching full capacity and drop down lists that gave options off a previous selection.

I knew that once I completed this Excel order form, ordering was going to be so much easier.  The customers could order exactly what they wanted and do so quickly.  The salesman wouldn’t spend time trying to translate the customer’s order, the order processor wouldn’t spend time trying to determine item numbers or pricing—everything would be consistent and simple for everyone involved.

Every time I think I’m good at a software program, someone challenges me to stretch my abilities.  It seems the more I learn about Excel, the more I realize I’ve barely broken ground.  So I spent a lot of time Googling formulas and have pages of notes on how I performed my calculations.  I used data validation, vlookup, hlookup, lists, if, named ranges and countless other formulas to finally compile a near perfect form for the salesman.

It worked pretty well the first year, but with a few bugs.  It worked even better the second year and was near perfect.  I was even able to start training others on how to make changes, what not to change and successfully passed along my work in progress to my predecessor.  It was a success and because I put effort into making it happen, I saved weeks of work for the salesman, the order processor, supply chain, and most importantly, the customer.

The moral of my story is to never limit yourself because you think you might not be able to do something.  If you don’t try, you won’t know.  There’s a wealth of knowledge on the internet to explain how to do things, so take advantage of it!  Always take precautions and stop if you find that you truly are unable.  But if you don’t try, you’ll never know whether or not you can fix a dryer yourself.

Advertisements

Video Tutorial: How to Use the Left & Upper Functions in Microsoft Excel

This video tutorial demonstrates how to use the LEFT and UPPER functions in excel.

Download Microsoft Excel

In this example, we have a fictional address list in which part of the data is in all caps and the rest is in sentence case. Additionally, some zip codes have the additional 4 digits and for this example, we only want to use 5-digit zip codes.

We use the UPPER function to change all the text to uppercase and the LEFT function to choose only the first 5 digits of the zip code. Also shown is paste values.

Video recorded using Cam Studio (www.camstudio.org)

Also be sure to check out my additional video tutorials on my YouTube channel.

Thanks for watching, I hope this was helpful to you!

Become Amazing at Excel: Recommended Reading

Video Tutorial: How to Use Format Cell, Auto Sum & Equations in Excel

This is a sample fictional budget to demonstrate how to use the format cell function, auto sum and simple equations in Excel. I demonstrate how to calculate a budget of monthly bills and extend the calculation to determine the cost of bills per year.

Download Microsoft Excel Now

These are standard formulas and functions everyone should know to make using Excel more efficient.

Tutorial was done in Excel 2010 but alternative options were shown for previous versions of Excel.

Video created using Cam Studio (http://camstudio.org)

Learn Everything There is to Know About Microsoft Excel

Video Tutorial: How to use the Concatenate Formula in Excel to String Data Together

This video tutorial shows how to use the concatenate function in Microsoft Excel to string text together and be more efficient.

Download Excel Now

Tutorial was done in Microsoft Excel 2010 but should be the same for all recent versions of Excel.

Here’s a super quick version demonstrating the concatenate function.

Video created using Cam Studio (http://camstudio.org)

Learn Everything There is to Know About Microsoft Excel

Video Tutorial: How to Create a Drop Down List in Excel Using Data Validation

This tutorial demonstrates how to create a drown-down list in Excel using data validation.

Download Microsoft Excel Now

In this video, we are creating a simple HR employee hire form and limiting the user to selecting numeric months and years from a drop-down list on a separate tab. The tab can be hidden from the workbook once the form is complete so the user cannot alter the data.

The tutorial was done in Microsoft Excel 2010, but the function will be similar in previous versions.

Video was made using Cam Studio (http://camstudio.org).

Learn Everything There is to Know About Microsoft Excel

Using the Concatenate Function in Excel to be More Efficient

If you use Microsoft Excel on a regular basis, chances are you probably have some rather repetitive, mundane tasks mixed in with your usually fun and exciting job. Personally, I would much rather work on something (ANYTHING!) in Photoshop than analyze data in Excel, but I lean more toward creative than analytical anyway.

Download Microsoft Excel Now

But alas, I am a business person and if I want to efficiently process any type of data, I  need to use Excel. I have also used it for quick html coding, but I’ll cover that in a future blog.

Today, I think you should know about the ever-so-helpful concatenate function.

Click here to view my concatenate video tutorial.

Concatenate is defined as the action of linking or combining. Excel offers this funtion to combine the information in multiple cells and you can add other information into the mix as well.

For example, say you have columns containing first name, last name and titles of 300 people and you decide you actually want that information together, such as John Smith, Baker.

Now you can spend the better part of the day hating yourself and re-typing each combination, hoping you don’t make any typos, or even for the speedy Excel user, you could copy and paste, but concatenate can make this action possible for all 300 in less than a minute.

Once you have your data in a spreadsheet and are ready to combine it, click in an empty cell in which you want the results, then click on the fx/insert function.

If “concatenate” is not in your recently used functions, just type “concat” in the search box and press “Go.” Select Concatenate from the list and press OK.

Now here’s where it can get a little intimidating, but a great feature Excel offers is the formula result, which shows how the final product will appear. Another helpful feature is you can simply click in the cells that contain the information you want concatenated into the order you want it concatenated. Don’t forget that if you want to add spaces or punctuation, you will also need to add that. You can either do so by adding it next to the cell number or in a separate text box, the choice is yours.

Once you have concatenated the string of data to your preference, it’s time to spread the love to all the other columns. This is super easy: click on the cell containing the formula, then position the cursor in the bottom left hand corner of that cell. The cursor should change from a large white plus sign to a thin black plus sign. Click on the corner of the cell, hold down the left mouse button and drag the formula to the bottom of your list.

Today I used the contatenate function to rename a series of product images going on amazon.com. I had an Excel sheet with the UPC code which is what the image name needed to be, but there were multiple part numbers so I needed to name them UPC#.pt01.jpg and so on. I could have added .pt01, .pt02 and .pt03 to each file name, but the more I handle each file, the more my chances of a typo increase. By creating the file names in Excel, I am eliminating human error as much as possible and yielding consistent results.

NOTE: When working with functions in Excel, if you will need to copy and paste the information elsewhere, be sure to copy the data and “Paste Values” so you’re copying and pasting the data, not the formulas.  Otherwise, you could end up pasting an error value instead of data.

Do you use the concatenate function? How has it helped you save time?

Learn Everything There is to Know About Microsoft Excel