The working girl's guide to Excel

How to impress your boss with more than your ability to accessorize

[jessafayeexcels]

As far as productivity goes, learning some of the shortcut keys for pasting will make you amazingly efficient. Data nerds, take a look and change your life.

[jessafayeexcels]

Create a searchable drop down list in Excel

This video will show you step by step how to create a searchable dropdown list in Excel. This technique uses data validation and some formulas. SEARCH, VLOOK...

Guys, this is my new favorite trick. If you have a large drop-down list this is the way to go!

[jessafayeexcels]

Hey Excellites,

Here's a great tutorial on how to create fancy Speedometer chart in excel. This would be great for a dashboard presentation.

[jessafayeexcels]

Guys! My second most favorite program is called Jing. It's a free screen capture that you can edit and it's how I make all of my Excel tutorials. Do yourself a favor and download!

[jessafayeexcels]

Navigating a cumbersome Excel workbook

Some Excel workbooks have a ton of worksheets or tabs that are a pain to scroll through. Never fear, Excel has your back.

1. Click on the “Insert” ribbon and click on Shapes. Any shape will do, but I like a plain rectangle so I can add text to it.

2. After inserting the shape in your worksheet, right click on it and select “hyperlink”.

3. From here you can select which worksheet and cell reference you’d like to go to.

4. You can also add a ScreenTip to let your user know what clicking the object does.

5. Now right click on the shape and edit text.

6. Voila! You now have an easy navigation system for your workbook!

[chat-me-up]

Hey baby, I bet you Excel between the spreadsheets.

(via chat-me-up)

[businessinsider]

Here are some essential keyboard shortcuts for Excel that everyone should know.

[jessafayeexcels]

Learn how to make a Dynamic Dashboard using Microsoft Excel. At the end of this tutorial, you will learn how easy it is to set up a dynamic dashboard using excel formulas and simple VBA macros.

My all time fave Excel tutorial!

[jessafayeexcels]

Advanced Lookup theory

Today, let’s talk about how to put the lookup function to work. Earn your keep, you lay-about!

 Looking up data vertically and horizontally.

We know from my post about VLOOKUPs that you can return data that is arranged vertically or horizontally with an HLOOKUP, but what if you have two parameters?

For Example, my data table looks like this:

  I want to query for Category 3’s percentage on 6/30/2012, so I’ll start with a basic VLOOKUP.

 =VLOOKUP(“Category 3”,G10:J18,2,FALSE)

Notice that I’m just throwing a “2” in the column number spot as a place holder. I’m going to replace it with the MATCH function to make a nested formula.

  The MATCH function will figure out which column to pull from.

  The formula will look like this:

  For the trick to work, the array portion of the formula is all of the header row. The 0 value tells the function to query for an exact match.

  Now replace the “2” in the VLOOKUP function with the MATCH formula.

=VLOOKUP(“Category 3”,G10:J18,MATCH("6/30/2012",G10:J10,0),FALSE)

If necessary replace the portions in quotation marks with cell links so that the formula becomes dynamic as opposed to hardcoded.

=VLOOKUP(B1,G10:J18,MATCH(B2,G10:J10,0),FALSE)

Your new nested function returns 5.50%

  Using an array formula to query for two parameters.

  Array formulas are really advanced, so I’m not going to go too much into depth on why it works, but how it can work for you.

  Array formulas are volatile, to make it work you have to CTRL+SHIFT+ENTER within the formula box EVERYTIME. If someone else clicks into the formula they will ruin the array formula until CTRL+SHIFT+ENTER is pressed again, within the formula box.

  Here’s the formula

  {=INDEX(Z:Z,MATCH(1,( F:F=I5)*(H:H=J5),0))}

  The brackets won’t appear until CTRL+SHIFT+ENTER are pressed.

  Here is a breakdown of how it works:

  It’s like magic ya’ll.

[jessafayeexcels]

Mail Merge Blues

A couple of weeks ago, we talked about organizing data like names and things of that nature. Now let’s create labels that you can print at home. Super easy to do and it makes your Christmas Cards manageable.

Here’s the trick with the mail merge…you have to do it in word. I know, it doesn’t make any sense. My data is in Excel, why can’t I just make labels from there? Because Microsoft is dumb sometimes you guys.

Open up a blank word document and click on the “Mailings” Ribbon. Click on the “Start Mail Merge” Icon and select “Labels”.

Now select your label company and product number. The standard is Avery 5160.

  Now select your data source by clicking on the “Select Recipients” icon and choosing “Use Existing List”.

Browse to the handy excel sheet that you created a couple of weeks ago and your label sheet should look like this:

  Now click on the “Match Fields” icon.

Make sure that the column headings are correctly matched to the fields. I added my City, State Zip column heading to the “City” field.

  Click on the “Address Block” icon and preview your first recipient’s output. Make sure everything looks the way you want it to.

Click the “Update Labels” icon.

  Your document should now look like this:

  Now click the “Finish & Merge” icon and correct font height.

Voila! Perfect labels every time!

[jessafayeexcels]

Have a burning Excel Question?

Drop me a line and I'll fix your Excel issues!

[email protected]

[jessafayeexcels]

Creating Christmas Cheer

Every year at Christmas, my mother asks me to help her compile a list for her precious Christmas Cards.

Not this time Jan Settles. Not. This. Time.

First she sends me the names in a .txt document that looks like this:

  What to do kittens?  Lets copy the data by CTRL + A and CTRL + C and pasting into a fresh Excel. It should look like this:

  Now let’s fix the orientation of the data to vertical:

Reselect the data by typing CTRL + Shift + End and CTRL + C (now the data is in your clipboard)

Right Click on any cell and choose “Paste Special”

Now choose the “Transpose” option and hit “OK”

Delete the original line and your data should look like this:

  For fun, let’s break up the first and last names, you may want to be anal retentive and alphabetize your labels.

Select the column of data that you’d like to separate.

Click on the “Data” ribbon and “Text to Columns”

Select “Delimited” (“Fixed width” only comes in handy when data is perfectly separated, which happens like never)

Select “Space” and deselect “Tab”. The data separation will preview for you.

You can skip the “Data Format” screen unless you are formatting numbers and dates and such. Just click “Finish”

Now add a “First” and “Last” name header.

Now Alphabetize by highlighting the “Last Name” column and click on the “Home” Ribbon, the “Sort & Filter” icon and the “Custom Sort” option.

A Warning box appears, click “Expand the selection” and “Sort”

In the sort box you can choose what column you want to be sorted, add a level if you want another column to be sorted as well and whether or not you want your data to be ascending or descending.

Now your data is sorted alphabetically and your mom sends you the addresses and she wants labels.

Tune in next post where I unlock the mystery of the dreaded mail merge.

[jessafayeexcels]

10 posts!

[jessafayeexcels]

How to format like a pro!

Excel is not Power Point and it is definitely not an Adobe product. There are some limitations, but with a little bit of know-how, you can be formatting excel like a contractor.

Brand yourself.

I use a set of tropes again and again as my brand of formatting.

Remove gridlines from user entry or output sheets

To show that a cell needs user entry I use a light yellow fill, hyper-blue text and grey borders. 

You can use specific colors by clicking on the fill or the text icon in your home Ribbon and clicking on “More Colors…”

From there you can use the Standard colors

or you can use the custom RGB or HSL to get exact colors.

After adding specific colors, the excel file saves them in “recent colors”

More tricks!

1. Add your company’s logo to the top of an entry form.

2. Add fancy shapes like arrows to draw attention. Click on the Insert Ribbon and click on Shapes.

Any shape (this includes charts) can be formatted. Just click on the shape or chart and an extra ribbon will appear specifically for formatting the shape.

  Let’s format a chart ala Jessa Faye Style

1. Take any set of data and create a bar or column chart

2. A new Ribbon appears named Chart Tools. Click on “Layout” and “Legend” and “Show Legend at Bottom”.

3. Now, right click on the first column in your chart and click “format data series”. This will let you format the color to a specific one. (Hint-use your company’s colors!) Then apply a gradient using the format ribbon and clicking the gradient option. Watch the Video!

Formatting a chart using gradients

*Note – a few colors do not have the gradient option in dark. Just use the solid instead.

Apply a light grey fill to your plot area and a border to your chart.

Voila! Perfect formatting every time.

[jessafayeexcels]

Automating Text

Sorry it’s been awhile Excellites, here’s something extra special to make up for it.

 A feature that comes in handy quite often is automating text.

Let’s say we have a situation where text is dependent upon a dropdown that is chosen.

Let’s set up an example that’s close to my heart.

In any cell, type in this formula:

=Today()

That returns the current date.

In another cell set up your dropdown list with Halloween, Thanksgiving and Christmas. You can refer to my post named “A life compiled in lists” or you can watch the video at the end of the tutorial.

  Now we’re going to set up a reference table on Sheet 2.

  Now let’s set up some text that will automatically populate dependent upon what the user chooses.

The trick with automated text is this - Hard coded text is within quotation marks, formulas or cell references are within ampersands, Like so:

  I plug in the = symbol to start the formula and immediately throw in the quotation marks to denote hard coded text. When I’m ready for a formula or for a cell reference, I encapsulate said reference in ampersands and close it out with quotations around the period.

  OK, now let’s make magic. Here’s the formula that I used:

="Johnny counted the days until "&HolidayDropdown&", of which there were "&VLOOKUP(HolidayDropdown,HolidayReferenceTableOnSheet2,2,FALSE)-Today’sDate&"."

And here’s the video!

 How to Automate Text

Text automation comes in handy when you have email or dashboard reports that are handed in frequently. For example, text explaining that your project is over or under budget by a specific amount. Just copy and paste the end result into your email! You only need to set it up once.

[jessafayeexcels]

Excel and your Philosophy 101 course in college

If all philosophers are thinkers and John is a philosopher, then John is a thinker.

You remember these kinds of statements, don’t you? It was the only thing I remember from possibly the most useless course I’ve ever taken. *Apologies to the two philosophy professors that I have since befriended.

Excel and quite a few other programs use this kind of logic to solve for answers. What I’m talking about is my favorite function, the IF statement.

Essentially, If statements work in three parts:

Pose a question or problem – Is cell A2 equal to cell A3?

If the answer is true, return a value – If cell A2 is equal to cell A3, return the word TRUE.

If the answer is false, return a value – If cell A2 is not equal to cell A3, return the word FALSE.

Excel breaks this down in an easy to manipulate manner.

=IF(A2=A3,True,False)

The equal sign and “IF” start the function followed by the open parenthesis.

The first portion poses the question and marks the end with a comma

The second portion tells the formula what to return, in this case the text True followed by a comma.

*Important! We could just as easily have the formula return a cell’s value or add quotation marks to denote to the formula that it needs to return text.

The third portion tells the formula what to return should the statement be false, in this case False, followed by a closed parenthesis.

And that, ladies and gents, is the mysterious IF statement broken down.

You can create your own by simply typing in the formula box. When you click inside the formula box the cells that the formula is referring to will outline in different colors. At this point you can move the cells around by dragging and dropping to your heart’s desire.

Creating an IF statement

Using the Formula Ribbon to create an IF statement

You can utilize different kinds of questions/hypotheticals for the first portion of the formula:

A2<=100 checks whether the number in cell A2 is less than or equal to 100

A2>A3 checks whether the number in cell A2 is greater than the number in cell A3

A2<>A3 checks whether the number in cell A2 is not equal to the number in cell A3

Next week, I’ll cover how to nest formulas within IF statements. You’ll be surprised at what adding a VLOOKUP to an IF statement can accomplish!

[jessafayeexcels]

The V-Lookup function

Hello excellites!

Want to return specific data from an array? No problem, let me introduce you to my little friend, the V-LOOKUP. The “V” stands for vertical (up and down). There is also an H-LOOKUP (horizontal), but in my experience, data is usually organized vertically.

The V-LOOKUP comes in handy when we want to pull one piece of data out from a data set. For example, here’s a set of data to copy and paste into your excel.

Number               Color                     Fruit

1                              Red                        Strawberry

2                              Yellow                   Banana

3                              Green                   Apple

4                              Orange                 Melon

5                              Blue                       Blueberry

6                              Purple                   Grape

Here’s a picture of how it will look:

  It’s a silly data set, but it will have to do. So, let’s say I need to pull the color associated with the number 3. On a separate tab, type “3” in to any cell. In the cell next to it, we’ll start our formula.

For beginners, I suggest using the Formula ribbon. Vlookups are located in the Lookup & reference icon. When you click on the icon, it gives you a list of all of the functions or formulas associated with that category. If you scroll over the function name, a definition of what the function accomplishes appears. Guys, Microsoft is feeding this to you!

  Once you click on the function, a function arguments box appears. All you have to do is click on cells or type in values. Every argument box gives you a definition. What could be easier, I ask you?

Let’s start with the Lookup_value. This is our “3” value. Just click inside the argument box and then click on the cell where you typed in “3”. Notice, that as you fill out your argument boxes, the function will fill out for you in the formula box.

  Now for the Table_array argument box. This is the full data set that we are pulling data from. Video here:

Table Array

  You’re so close! Now, here’s the tricky part, the Column index number. The lookup uses the column number not letter of your data set.

If I want to pull the color then I will enter column 2, if I want to pull out the fruit then I will enter column 3 etc.

  Notice that we are already getting a result of “Green”

The last part is the easiest, Range lookup. The argument box is asking for a logical (true or false) value. I ALWAYS use FALSE because I want to find an exact match.

*Note-if you need more examples or tips, just click on the “Help on this function” hyperlink.

You have completed your first advanced function! Huzzah! Just click OK and watch the magic happen.

I’m going to wrap this up with a video and a little formatting magic.

Application of V-Lookup

Don’t worry, this is just the beginning of fancy formulas tips and tricks!