microsoft office

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

Advertisements

My “Man V. Food” Method to Getting Re-Organized Using Outlook Tasks

My mother once told me my biggest strength was my organizational skills. I must admit, when I put the effort into it, I am an incredible organizer. I love having everything in its particular place, paperwork filed, bills paid, laundry sorted and things very tidy. It just makes me feel better.

However, lately, I’ve fallen out of it with travel and my husband getting in an auto accident, which resulted in a mound of paperwork that has been consuming my evenings. When I fall out of my organization, I start to panic and I can end of focusing on being overwhelmed with tasks rather than completing them.

This does no one any good.

My position at work is an interesting one. This is my sixth year and my third position at this company so I’ve accumulated a lot of responsibilities. There are things I really have no business doing, but ultimately, I am the one most suited to do the task. I have one manager, but also report to other managers and receive multiple requests from all departments each day. Organizing my work can be quite daunting, but is incredibly necessary if I want to succeed.

I am never without my trusty notepad. My current one was started almost a year ago and tracks all requests, phone messages, ideas, you name it. The problem is when my tasks increase, it can get difficult to get a good view of what I really need to accomplish unless it is nicely summarized in an orderly fashion for me. Sound familiar?

THE METHOD

So what’s a good way to get started? Tackling all tasks at once is too overwhelming. Think of any of the Man Versus Food episodes when Adam Richman has several pounds of food to eat; he always starts with a game plan of the most efficient way to eat the food.

Think of your to-do list just like Adam thinks of his eating contests:

  • What is the goal?
  • What is the time limit?
  • What can be done in quick nibbles?
  • What is going to require a bunch of steady bites?
  • What can be cut into smaller pieces to finish bit by bit?

Viewing your workload in the Man V Food method makes tasks easier to accomplish because you can view each task as a bite of your work meal.

ORGANIZATIONAL PROGRAMS

There are tons of task management websites and software available, but sometimes, it feels like that’s an extra task in itself. I started with a free account with ToDoist.com and I was pleased with the initial features. However, to really get what I wanted out of it would cost $29 a year which is very reasonable, but I decided I would stick to my Outlook Tasks instead.

If you’ve ever used your tasks function in Outlook, you may remember how annoying it can be. Tons of red items popping up, reminders you keep having to dismiss, followed by canning the whole thing and ending up a dis-organized mess again. But, when used correctly, Outlook tasks can be a great tool.

Here are some features I really like:

  • Status: You can update the status of each task as a reminder. Waiting on an answer from someone? Simply update the status and set a reminder to follow up.
  • Send Status Report: Maybe you have a list of tasks required by one person. You can create a task for the list to keep tabs on everything required, format using bullet points, highlighting, whatever you want, then set a reminder to send them an update at a specified time. Simply click on Send Status Report and send them an update in one easy email.
  • Assign Task: Maybe you need others to complete tasks for you. Just enter the details in a new task and send to the recipient. Set reminders for them and request to be notified when the task is marked as complete. Set recurrence for recurring tasks.
  • A LOAD of sorting options: Outlook provides multiple options to customize the view exactly how you want it. I like having a preview window below so I can see the details of each task, sort each task by due date, separate them by how soon each is due and have status showing.

PHYSICAL ORGANIZATION

Your desk might be covered in stacks of paperwork as well, adding to the stress of your workload, but this is manageable as well.  Sort paperwork into sections as applicable and stay on top of filing when tasks are completed.

Here’s what works for me:

  • To be handled/current projects: this is a place for anything I’m currently working on that I might need quick access to.
  • Waiting: this is where I put paperwork that is pending approval or a response from someone.
  • To be filed: this is where I put paperwork when it is completed.  Set aside a time each day or week to file this paperwork so it doesn’t pile up.

By getting yourself organized better, you will be able to accomplish tasks more efficiently and completely and show off your competence to complete whatever comes your way.

How do you stay on top of all your tasks?

Increase Your PowerPoint Efficiency Using a Slide Master

One of the easiest ways to streamline your PowerPoint presentation is by using a Slide Master.  Slide masters are the skeleton of your presentation where you can define the complete design of your presentation and make universal changes to your presentation by altering one slide.  The slide master enables you to tell PowerPoint exactly what you want your presentation to look like and should be used every time you create a presentation.

How to Access the Slide Master
In current versions of PowerPoint, you can access the slide master by clicking on the View tab and selecting Slide Master.

Accessing the Slide Master

Accessing the Slide Master

What to do with the Slide Master
Once you are in the slide master view, you are presented with the slide master (the top slide, labeled with a 1) and a variety of layouts to customize.  Start customization with the  slide master:

    • Master Layout: use to add place keepers header/footer, date, slide number, title or text.  This will place boxes on the slide that you can customize, such as adding your company URL to the footer.
    • Themes: use to change the colors and background image.  You can also add your own custom background.
    • Colors, Fonts, Effects: use to customize the appearance of your text

Customizing Layouts
Once you have customized the master slide, you can customize the layout of the other slides.  You may not know exactly what your slides will need to look like, but chances are, you know you will have a couple slides with bullet points and one image, a slide comparing two images with bullet points and so on.  The layout slides allow you to customize how each of these slides will appear.

You can move the text boxes to fit better with your background, select where images will be placed or set a place keeper for a chart you will be inserting.

Using the Slide Master & Layouts
Once  you have edited the slide master and are ready to work on your presentation, click on Close Master View from the Slide Master tab to return to your presentation.  Add new slides by clicking ctrl + M and select the layout by right clicking on the slide, click on Layout and selecting the layout of your choice.

If at any time you need to make a change to the slide master or a new layout, go back to the slide master, make your adjustment, then return to your presentation.

Things to Keep in Mind:

  • Don’t stick with the default.  If you’re trying to impress, sticking with the default Times New Roman font and standard colors tell your audience that you didn’t put a lot of effort into the design.  Take a little time to choose a design that matches what you are presenting to give a more tailored feel to your presentation.
  • Keep it clean and simple.  Make sure your presentation is easy to read, the font color contrasts with the background for ease of reading and the font size is large enough (but not too large) for all to read.  Be sure to keep your presentation on the eye-catching versus distracting side or you will quickly lose your audience.
  • Mind the file size. Graphically-heavy presentations quickly build up to large file sizes and if the file size is too large, your or your recipient’s email client may reject it for being too large.  Use a cloud or drop box to easily share files over 10MB (some clients don’t allow over 5MB) and compress images using the Compress Pictures feature on the Format tab. Note compresses images can greatly decrease the quality so try re-sizing prior to inserting in the presentation.
  • Mind PowerPoint version compatibility.  What looks incredible on PP 2010 can look awfully strange on PP 2003 or XP.  Granted, by now, most companies should have more current versions, but for those who do not, your incredible presentation could look terrible to them.  If you are sending your presentation to a client, it may be a good idea to save it as a pdf so you know exactly how it will appear to them.  (Need a pdf writer? Try CutePDF free pdf writer!)

Using the slide master is one of the easiest ways you can make a professional looking presentation with ease.  For more information, check out the Microsoft PowerPoint Slide Master Page.

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

13 Keyboard Shortcuts Everyone Should Know That Will Make You More Efficient

I don’t work in the IT department, but I’m tech-savvy enough to know more about computer stuff than a lot of my coworkers.  Because I’ve worked so much with software programs and strive to always find the most efficient way to complete tasks, I’ve realized there are shortcuts I’ve taken for granted that everyone using a computer knows.

Download Microsoft Excel Now

But not everyone knows all the incredibly useful shortcuts that could save them loads of time when working on just about anything that requires a keyboard.  You may even have these shortcuts printed on the front side of the keys on your keyboard (“Oh, that’s what those are for!”)

So here is my list of useful keyboard shortcuts everyone should know :

NOTE: you do not need to type the “+” in these.

  1. Ctrl + a – select all.  This is handy if you want to select the entire contents of a document or website and would usually click and hold the mouse button, scrolling down the page and hoping you didn’t miss anything.
  2. Ctrl + c – copy whatever you have highlighted.  Instead of clicking Edit – copy (or however your method may be depending on your program), two buttons, when pressed together do the work for you.
  3. Ctrl + x – cut whatever you have highlighted.  If you want to remove words or an image, but keep it on your “clipboard” for later use, this would be the option for you.  Just don’t wait too long and forget that it’s waiting there to be pasted or you might copy or cut something else and lose it.
  4. Ctrl + v – paste whatever you’ve copied or cut.  Just click where you want it to go, press the magic keys and you’re in business!  Just remember that while it may seem logical to use Ctrl + p for pasting (that’s reserved for printing), notice that x, c and v are lined up across your keyboard, so sequentially, it makes sense.
  5. Ctrl + p – as we just learned, this is for print, not paste.  If you’re on a website and only want to print a selection, highlight the section you want to print, press Ctrl + p and when the print window pops up, make sure “Selection” is selected as the Page Range.  Otherwise you will print the whole page.
  6. Ctrl + s – save whatever you’re working on.  If you are neurotic as I am about making sure you don’t lose your work, this should be well-programmed into your brain.  I also set the auto save to 2 or 3 minutes, down from the default of 10 minutes (hey, I can type a lot in 10 minutes!) but if I’ve done some exceptionally detailed work, I often use this shortcut just to ensure I’ve saved my work.  This will also bring up a save dialog box in case you haven’t selected a filename and save location already.
  7. Ctrl + b – bolds the highlighted text or if no text is highlighted, bolds subsequent typed text.
  8. Ctrl + u – underlines the highlighted or subsequently typed text.
  9. Ctrl + I – italicizes highlighted or subsequently typed text.
  10. Ctrl + f – brings up a search box to find text within a document or web page.
  11. Ctrl + h – find’s ultra-helpful older brother who will find text then replace it with text you specify.  Very helpful if you find out you’re misspelled a name or referred to an incorrect location multiple times in a document and want to be sure you’ve fixed all occurrences.
  12. Ctrl + n – usually creates a new document or workbook.  To create a new slide in PowerPoint, use ctrl + m.
  13. F2 – allows editing in an Excel cell or when text is highlighted.  It’s a bit difficult to explain the awesome functionality of this, but here goes: especially when working in Excel, if you copy a cell, then paste in a document or elsewhere, you may notice a box around the text or strange box characters at the end of the pasted text.  You may also find that if you are editing a cell, sometimes all the contents get highlighted and you write over it.  By pressing F2 while on a highlighted cell, you can easily edit without writing over the text already in the cell.

Once you learn these shortcuts, you will find you are saving loads of time and working much more efficiently.  You can even often create your own shortcuts and learn even more not listed here that will help speed up your work.

Click here for more Microsoft Office shortcuts