Tips for Excel, Word, PowerPoint and Other Applications

Welcome

The goal of "Tip of the Week" is to promote understanding of the WHY for Excel, Word, and Powerpoint. It isn't good enough to understand HOW to make a pivot table if you don't understand WHY it's not generating the results you want. Knowing how to add text boxes and charts to a PowerPoint slide isn't the same as crafting a message that makes people sit up in their seats. Unfortunately, the learning curve is steep and takes an investment of time, patience, and effort. However, with your help, we can pound that learning curve flat and all go home a little earlier.

New Articles and Tutorials

10/5/08

It's been a while with everything going on at work and the kids officially starting Kindergarten. My schedule is all out of whack and I've been travelling a bit. Sitting on the plane is a good time to collect some thoughts and tie up some loose ends. I finished an article on Text Functions and another on Helper Columns. Neither topic is rocket science, but when you incorporate these abilities in your tool set, are quite useful.

9/1/08

We are officially 1-year old. I can't believe that I started this only a year ago. In that time, I've gotten 81 articles written up and I think I've been able to help a lot of people out. It's been fun and I've learned a ton in the process of re-writing my tips.

8/29/08

OFFSET has interested me for a while. I've seen it used in the Excel discussion groups, but never really had an opportunity to play around with it until now. It's pretty nifty. Here is my write up on OFFSET and Dynamic Ranges.

8/22/08

I was asked by someone at work, "how do you print out webpages so that the right sides don't get cut off?" so that she could print out the Tips. After some quick investigation, I found an answer and wrote it up, Printing Web Pages.

8/21/08

Kind of a sister article to VLOOKUP-MATCH is INDEX-MATCH. The example I use is looking at a range of bids and returning the name of the lowest bidding vendor for each line item, but you can use the same structure to accomplish a wide range of things.

8/13/08

Added a short tip on, VLOOKUP-MATCH combos. This is an easy way to return a value from a table.

8/8/08

One of the questions I see all the time is, "how do you compare two lists in Excel?" Assuming you have consistent spellings (e.g., the names you want to match are all spelled the same between the lists), then there are two quick ways of doing this. The article, Matching Two Lists in Excel, will walk you through using COUNTIF or VLOOKUP to compare lists and provide feedback you can use to append supplemental information.

8/5/08

Back from QuakeCon. Here's a very useful tip, A Quick and Easy Way to Fill in Blank Cells, which is a very fast way to fill in all of those nasty black cells after running a pivot table or if you receive a data set with lots of subtotals.

7/24/08

Quick Tip: Did you know that SUMIF and COUNTIF work with inequalities. If I have values 1, 2, 3, and 4 in cells A1:A4, I can use =COUNTIF(A1:A4,"<3") to return a value of 2 and =SUMIF(A1:A4,"<3",A1:A4) to return a value of 3. This also works with dates, which would look like this: =COUNTIF(A1:A20,"<7/20/2008"). Your inquality has to be encased in quotes in order for this to work. If you forget the quotes, you'll return a value of 0.

7/22/08

Finally figured out how to format org charts in PowerPoint with some help from the Microsoft PowerPoint discussion group. Now I know how to change styles and shapes as well as font, fill, and line colors.

7/17/08

Put up another quick note on Advanced SUMPRODUCT, this time, using blank or not blank status of a cell as the summing criteria. Take a peek at the article. By the way, I started poking around the Microsoft Excel Discussion boards. Very interesting stuff there .... makes you appreciate how little you really know. Definately a lot of interesting material to write about though. I expect that I'll be able to get some good material to pass on.

I also started a new page for all of the Quick Tips, which I will keep adding to as I run across cool new things.

7/16/08

Quick Tip: I found this tip at Just Pivot Tables under the July 4th entry ... hmmm, another person posting tips on the 4th of July. Anyways, if you're a user of pivot tables, but you hate having all of those subtotals messing up your data table, there is a really easy way to get rid of them ... it's built into the Pivot Table Wizard. When you're in the layout view, simply double click on one of Row elements, and you should see the following box pop up. Simply click the radio button to none, then OK, and you're done. No subtotals.

7/15/08

Ever been frustrated by PowerPoint automatically rearranging your page layout when you insert a new time? This is a function of AutoFormat, actually AutoCorrect to be more precise, and thankfully, there is a way to turn off AutoCorrect options that will stop PowerPoint from doing this to you.

7/5/08

I had this one done a week ago, but forgot to post it. Bullet Charts are a good chart type for measuring metrics against a target. Unfortunately, Excel doesn't support them natively, so you kind of have to hack a standard stacked column chart to make them work. Fortunately, it's not that hard, you just have to work through a couple of things.

Quick Tip: The AutoFill function, the small black grab handle on the lower right hand side of the active cell, can save you a lot of time. You already know that you can grab it and drag in any direction to copy a cell's contents and formatting to adjacent cells. However, there is a very cool shortcut if the column to the right is already populated. If this is the case, format the cell at the top of your target column as desired, then double-click on the AutoFill handle. Your cell will get copied as far down as the column to the right has continguous cells. This also works for standard series. If you have an odd series, like 1-3-5-7 etc, then, you can enter the first 2 values, highlight, and then double-click the AutoFill handle. I'll write up a tip on this in the near future, but thought it was interesting enough to share.

6/20/08

The Watch funtion in Excel allows you to monitor the value of selected cells, regardless of where you are in the work book, which is very useful when you're building multi-sheet models or analyses and have inputs scattered throughout.

6/13/08

So, we finally get to Part 3: Cameras, Copy Picture, Paste Picture Link and more really cool stuff. Now that you understand how to work with graphics in Excel, we can start doing some really interesting things with dynamic pictures. I think the next major tutorial will be how to create those very cool traffic lights.

I've also got some other tutorials in mind. Julian dates. I actually had to help someone through a Julian date conversion the other day. Named formulas and bullet charts. Good stuff coming.

6/9/08

If you've ever had to manage large data models and don't already know about the Watch function ... rejoice! Watch allows you to dynamic watch the value of selected cells regardless of where you are in the workbook. This allows you to change key inputs while seeing the effect on downstream values.

6/6/08

Quick Tip: If you have Adobe Acrobat and a PDF that was created from the original Word document, you can use the Save As function to convert the PDF back into a Word document for easier manipulation and edits ... unless it was protected.

6/3/08

The next foundational piece I'll address before moving onto the dynamic pictures is a short discussion about object layers and color transparency. Excel is used as the example application, but the principles are the same whether you're adding picture objects in PowerPoint or Word.

I also made some minor updates to my Advanced SUMPRODUCT articles and added a third example, where we use Advanced SUMPRODUCT to count only the odd values in a list.

5/29/08

I spent some time over at ExcelUser trying to figure out the mechanics of traffic lights and I think I finally figured it out. Here's an example of what dynamic pictures and camera objects can do for you. This is what I'm driving for over the next couple of articles.

If you open up the Traffic Light example file and take a close look at the picture to the right, you'll notice all sorts of cool things going on. We'll eventually get to all of this.

5/26/08

The first installment of our discussion regarding pictures in Excel. Before I can describe and discuss Picture Links and Camera objects, I think we need a preliminary discussion about the Excel drawing toolbar, which I'm addressing today and then a discussion about layering and object order. I think this will help set some context around why the picture objects can be so useful.

5/20/08

I realized that you can secure a PDF document after creation, so I've added that write up and because the article was getting long, split up the Securing Acrobat Documents and Securing Winzip Archives sections into separate articles. Each now has its own write up.

5/19/08

I discovered something very cool - Excel's ability to support dynamic pictures. I had actually read a tip in one of the PC magazines about Copy as a Picture, but hadn't gotten around to trying it out. Then, I found a number of articles about dashboards on ExcelUser, which referenced the Camera tool. There is also the ability to Paste Picture as Link. Between these three tools, I think there are some very cool things you can make Excel do for you. Now I just need to organize some tutorials to walk people through the concepts.

5/18/08

Sometimes we need to calculate the number of work days between two dates. It's even more useful if we can account for holidays and other non work days. The standard suite of Excel formulas doesn't really have anything that can help us. Fortunately, you can find a nifty little function called NETWORKDAYS in the Analysis ToolPak that is very easy to set up and use.

5/13/08

With all of the concern around security and privacy, it pays to understand the password protection capabilities packaged with Adobe Acrobat and WinZip. I've written up a short article detailing how you can add passwords to PDF documents and .ZIP archives.

5/10/08

I added two more site links to the Resources Page. There is ZettaLogix, who are a small-medium sized business consultancy with a very nice section on free Excel templates and forms. If you're looking for one for your business, this might be a good place to start looking. I've also added Computer Tips, who have a very extensive collection of tips across a number of computer and networking issues.

5/9/08

Been a while since I've posted anything new. Just came back from Disneyland so I'm still catching up at work.

Quick Tip: Have you ever received a .DOCX, .PPTX, or .XLSX documents and had no idea what they were or how to open them. These documents were created in the 2007 versions of Office and not openable with the 2003 or earlier versions without a compatability pack from Microsoft. However, you can easily go to the Microsoft page and download the compatability pack for free. This may only pose a problem if you don't have admin privileges to install files.

4/29/08

I learned this from a co-worker earlier this year, attaching e-mails in Outlook. This is quite cool and in many instances a better option than simply forwarding a chain, or chains, or e-mails.

4/28/08

I decided to carve off the green articles into a separate section of this site, called Think Green and keep all of the related articles indexed on that page, that way I can keep this piece focused on Excel and other Office Productivity tips. Why the interest in green? To be honest my current employer, Fireman's Fund and our global parent, Allianz SE have a number of intersting green initiatives underway that I am interested in. Also, there's an awful lot of hype in the market and I have some interest in separating out the facts from the hype and figuring out the value of what's left.

4/23/08

Updated and finalized my article on Carbon Calculators.

4/18/08

Quick Tip: If you need to select a large number of rows quickly, you can combine 2 shortcuts. CTRL + SHIFT + Arrow lets you select a continuous column of data. SHIFT + Spacebar, changes that selection into full rows, which you can then delete or format at your leisure. CTRL + Spacebar does the same thing for columns.

4/17/08

Spinners, Combo Boxes, and Buttons, Oh my! Today, we provide an introduction to Forms Tools and show you how to add some nice interactive tools, like option buttons, dropdowns, and sliders to your spreadsheet. A future tip will address some ways to tie these into various formulas you might write.

Quick Tip: In PowerPoint, if you want to resize an autoshape, but you want to keep the same size ratio, hold down SHIFT and then drag one corner to the size you want. This also works when you're adding a new shape, for example a circle. Hold down SHIFT while you describe the size and it'll will stay a perfect circle.

4/15/08

Here's something different. I decided to take a look at a number of carbon calculators and compare the values they returned for my household. I took a look at 11 sites, of which, 7 had their own calculators. The number, or more importantly, the disparity of figures is quite interesting. The article is still a work in progress, but I've linked the current draft, which contains a table of values, notes, and links.

4/14/08

The Feb 2008 edition of PC Magazine, had a really good article about 157 free software tools and applications. Some of them, like Adobe, Google Toolbar, and Irfanview, I've already discussed here. Others, I've tried or am using at home (Open Office, Plaxo, Skype, etc). Anyways, we're always looking for applications that make our lives easier and those that are free are even better. Just thought I'd share.

4/12/08

Today, something a little more prosaic. How about Arranging Documents for Better Workflow? The Window sub menu has some nifty, but often unexplored, commands to help you arrange documents side by side or vertically for easier comparision and review. Simple? Maybe. Worth knowing about? Definately.

3/23/08

Here's a short bit on using various Excel functions to create scorecards and report metrics. As simple as this sounds, it actually involves VLOOKUP, conditional formatting based on cell value and formula, and the Character Map.

Quick Tip: If you need to paste a section of an Excel workbook as a graphic, you can do this in one of two ways. The first way is to highlight the cells you want to copy, Ctrl C to copy, and then in your target application, go Edit: Paste Special and the select Paste As Picture. Alternatively, you can hold SHIFT and then go the Edit menu, where you will see a Copy Picture option. Choose the As shown on screen and Bitmap options. I found this one in PC Magazine.

3/19/08

Uploaded the second half of the discussion on Conditional Formatting, this time addressing Formula Is. This is a pretty neat function and lets you do all sorts of creative things.

I also answered a e-mailed question about how to selectively sum only parts of a list of values, for example, just the odd values. After a little bit of experimentation, I found a way to do it with the trusty Advanced SUMPRODUCT function. The actual example file can be found here. The actual formula looks like this: SUMPRODUCT(B5:B14,--(RIGHT(B5:B14/2,2)=".5")), which basically only includes those values where there is a remainder of .5 (odd values). You can also use this to only include values where another column isn't blank.

3/10/08

Conditional Formatting is a very nice tool that can help you highlight cell values that meet specific criteria you define. Conditional Formatting comes in two flavors: Cell Value Is and Formula Is, which will be addressed later. As the name implies, Cell Value Is allows you to define formats based on the value of the cell vs. the criteria.

Quick Tip: SHIFT + F11 will insert a new worksheet in Excel.

3/7/08

Quick Tip: If you need to take a screenshot, you can use the print screen key, PrtScn, which is usually located near the number pad. PrtScn will take a picture of your entire screen and save it in memory. Open up any application, and hit CTRL+V (paste) to view it. If you only need a screenshot of the active window, you can use ALT+PrtScn and then paste the resulting file to your favorite application.

3/4/08

Woo hoo, posted my 50th tutorial. Just a nice little personal milestone to celebrate quietly. I still have tons of things that I have written and need to mark up in HTML as well as articles I want to write.

Quick Tip: Do you find yourself always searching for the same folder (maybe a network folder) or application (Character Map or Calculator)? If you want to put a shortcut right on your desktop, find the target object, then right-click on it. From the menu bar, select Send To, then Desktop (create shortcut) and a new shortcut will be created.

3/1/08

SUMIF and COUNTIF have some neat undocumented capabilities also, namely the ability to use it with wildcards (*). That means, you can write a SUMIF formula to summarize everything named Jon, Jonathan, and Jonathan Smith. In any case, check out the tutorial and example.

Quick Tip: To quickly enable AutoFilters, highlight the title row (SHIFT + SPACEBAR), then use the shortcut ALT + D, F, F. To quickly clear all of your filters, the shortcut ALT + D, F, S, will show the entire data set.

2/28/08

The more I use the capabilities of Advanced SUMPRODUCT, the more I like it. Today, we'll talk about using a portion of a cell's content as the conditional test in an Advanced SUMPRODUCT array. I got to use this at work and it is pretty damn cool. You can find the discussion here.

2/24/08

Someone asked me the other day, "what qualifies a person, for resume purposes, as an 'intermediate' or 'advanced' user of Excel?" I'm not really sure there is a good list of "things you must know" that differentiates you between an basic, intermediate, or advanced user.

I think it really comes down to your level of comfort around being able to take in any data set, in any format, and being able to generate the desired analysis in a relatively quick manner. I would argue that, to your boss or potential interviewer, it doesn't really matter HOW you produce results, rather that you ARE ABLE to produce them. Take two simple formulas like VLOOKUP and SUMIF. Both are pretty easy to teach to a basic user, who may have no idea how to apply them to a data set. An advanced user however, can use these to group and transform a data set and generate some fairly interesting executive level reports. Throw in some text parsing and advanced SUMPRODUCT and the limits of what you can do expand significantly.

So, it really comes down to how you use Excel, not what you can do, mechanically, with it. This in turn, is dependant on what data sets your business client uses and what kinds of answers they need to extract from the data set. This also implies that there are considerations around your level of comfort moving data from other formats into Excel and completed analysis out of Excel and into other formats.

Therefore, I would argue that if you ever need to make a case for your Excel prowess, during an interview situation, the appropriate discussion to have is around how your interviewer obtains, analyzes, and uses data. This discussion allows you to frame your Excel proficiency in terms of what their needs are and how you'd directly add value. After all, you can spit out a list of functions you can do, but you have no idea whether your interviewer understands any of them.

1/13/08

First post of the new year. I've been busy with house projects and work, so I haven't written up too much. However, I've been working on a series of large data set of employees, cost centers, contingent workers, and work location across multiple suppliers and business units, which has been challenging to say the least. It really makes you appreciate how much value there is to control of data to ensure data conventions are observed. In this endevour, the combination of pivot tables and VLOOKUP has been a invaluable tool to help me make cross-associations. The pivot table gives me a unique list of values, which represents 100% of the data to be mapped. The VLOOKUP allows me to assign a normalized value to it that is consistant across all of my data sets.

On a more geeky side, I found a nice way to launder data from Excel through Word and Notepad to transform a long table of data into a .CSV (comma seperated values) file for upload into a database. I'll have to write both of these tips up when the month is over.

12/23/07

Quick Tip: Need a quick graphical view of your data? Highlight the data series, click the Chart Wizard button, select a chart type, and then click on the "Press and Hold to View Sample" button to preview the chart. Click Cancel to exit the chart wizard.

12/18/07

Uploaded a new article on an Introduction to Arrays. These are very powerful tools, though they can be a little confusing at first.

12/18/07

Just yesterday, I was reminded of a point I made a couple of months back, make sure your PowerPoint slides work in black and white. In my case, our color printer stopped working and I realized all of my shaded boxes looked the same. Had I followed my own advice, I would have either used a larger color variance or changed the border styles slightly to ensure that the black and white version remained as understandable as the color version.

12/17/07

Quick Tip: If you need to take a screencapture, you can use PrtScn (above your Backspace key) to take a snapshot of the entire desktop. Better yet, use ALT + PrtScn to take a screencapture of just the active window. Paste into Word, PowerPoint, or MS-Paint to manipulate further.

12/15/07

I was Googling for the Vista equivalent of Windows+D to show all of your recent documents and I ran across this nice article on Windows XP shortcuts on your keyboard, most of which seem to work in Vista too. It's a good read and has some pretty good shortcuts that I will definately be using too.

Added an article on how to create custom lists in Excel. Now, instead of typiing A-Z everytime you need the alphabet, you can type in A, drag, and autofill the entire series.

12/12/07

Quick Tip: Need to archive important e-mails and store them with other related files? If you have Adobe Acrobat, you can select the Print: Print as PDF option, which will create a PDF file that you can rename and store anywhere you want. If you don't have Adobe Acrobat, Microsoft Vista has some Document Writer options from the print menu or you can download a free reader like Cute PDF.

12/10/07

Well, my replacement router finally showed up and after a brief absence, I have returned. Anyways, I've been making entries to the front page, even while my internet connection was down, which is why you see entries on the 5th and the 7th.

12/7/07

Isn't that odd? I'm working on an article about arrays and today, someone at work asked me about arrays. It never ceases to amaze me how coincidental articles-in-progress and questions from work are.

12/5/07

Quick Tip: Holding down CTRL while using the mousewheel will zoom you in and out of just about any document. This works with Word, Excel, PowerPoint, WordPad, web pages and PDFs. It will not work with .TXT documents.

12/1/07

Quick Tip: Ever spend hours trying to track down the author of a printed PowerPoint deck? Why not add a meaningful document ID to all of your presentations? In PowerPoint, you can edit the Slide Master by going Menu: View, Master, Slide Master (ALT, V, M, S). Changes made here are universal to the entire document, so this is a good spot to add the document name in the corner, as a reference whenever it's printed. The convention I like to use is [title]_[department]_[initials]_[timestamp]. This way, people can backtrack anything I write back to me and I can find it for them. This is even more powerful if your company has document naming conventions.

11/27/07

It's amazing how Excel builds on itself. What started out as a fairly simple inquiry that I wanted to write up as a tutorial snowballed into a list of building blocks that I have to write up first. Anyways, here's a quick summary of SUMIF and COUNTIF. Good basic functions that have a place in a lot of spreadsheets.

11/26/07

Been a while since I've posted anything. Anyways, I have some more tutorials in the works. While I'm working on those, here's a quick tip if you're ever trying to search and replace the * character in Excel. If you can believe it, I discovered this from my brother while we were preparing Thankgiving dinner.

Quick Tip: If you're trying to search and replace * symbols, you can't use * (wildcard) as your search criteria otherwise you'll replace everything. If you use ~* as your search criteria however, the ~ acts as an escape character that enables Excel to treat * as any other character instead of a wildcard. Apparently, this isn't a problem in Word, just Excel.

10/16/07

Quick Tip: In Excel, CTRL+1 brings up the format cells menu. It's a much faster way of getting to this than Main Menu: Format: Cells

I also added a search box over the weekend. Hopefully, this will help you find what you're looking for, even if it's not on this site.

10/15/07

I found myself falling into the same trap as other Tips. I get so excited about something, the article turns out longer than expected, so that I can cover everything I want, but because of that, it never gets finished. So, I broke up my Advanced SUMPRODUCT article into 2 pieces. Advanced SUMPRODUCT, part 1 just got uploaded. Part 2 will come a little later, but I hinted at what I want to cover.

10/14/07

Been a while since I've posted, but I am working on an Advanced SUMPRODUCT article that will also relate to VLOOKUP on multiple criteria. It's been pretty busy at work so my time is a little stretched, but I'm hoping to get it and the example file posted soon.

9/30/07

Added 3 articles this weekend; 2 serious ones and a fun one.

1. E-mail etiquette. My take on it anyways.
2. Simple intro to PowerPoint menus and work panes.
3. Making custom "art" in PowerPoint

I wrote up the last article, how to make custom graphical elements, like the one on the right, in PowerPoint. Why? Not everyone has Photoshop or a slick little graphics program or sometimes we need to just whip something up for the kids (or for work). Tons of people have PowerPoint, and if you're creative, you can actually do quite a lot with it, so have at it.

9/17/07

Slowly adding to my portfolio of PowerPoint articles, this time focusing on how PowerPoint pages should be a laid out. Ditch some of the fancy graphics and transitions and focus on putting together a punchy, well structured page that makes a point.

9/17/07

Quickly consolidating similarly formatted data sets is not only a time saver, it's a life saver. When you have both the need to consolidate large quantities of data for analysis (e.g., RFPs, scorecards, etc) and the ability to control the form and format in which the data is returned to you, the INDIRECT function is an invaluable tool. I've written up an Intro to INDIRECT to share the function with you.

9/9/07

Added articles on Data Validation in Excel and Autofiltering in Excel. These two really work nicely together, because one allows simple filtering by discrete criteria (AutoFilter) while the other allows you to control inputs (Data Validation). This is particularly useful if you're sending out spreadsheets for external input and want to what can be entered to streamline the back end analysis and summary.

Also posted an article on how to dynamically link Excel content, tables and charts, in Word, which is very useful when you have standard reports being distributed on a regular basis. This saves a lot of time and keeps you from having to constantly fiddle with formatting.

9/8/07

I've moved the site out of testing and into a more public venue, where I'm going to start working on some promotion. Over the past two days, I added an article on Charting in PowerPoint as well as articles on two types of charts PowerPoint doesn't support out of the box: Waterfall Charts and Comparative Series Charts.

9/6/07

Well, I just tossed up a very high level Overview of Excel and a walk through on Charting in Excel.

9/5/07

Just finished posting my 30th article - Data Stacking. I'm going to try and get another 5-10 posted and collect some more feedback before I move the site out of semi-secret test mode into the public light.

9/2/07

Uploaded a fair number of articles over the weekend, including several on VLOOKUP:

• Introduction to VLOOKUP
• VLOOKUP and IF/ISERROR combos
• Using VLOOKUP to Match Lists
• Cell Parsing: LEFT, MID, RIGHT

9/2/07

Added my first real article, Introduction to Logical Statements and Logical Math, which includes both the discussion and a example spreadsheet. Second article added, Introduction to Basic Pivot Tables

8/31/07

Well, we now have at least a page for every tab. Wrote up my bio, ruminated on some modeling philosophy, and ported some other discussion material.

8/31/07

Well, I'm really just getting off the ground and trying some formatting and layout things. Really just a test for concept and layout right now, but as I convert tutorials, I'll try to post them so that people who come across even the test site can get a better sense for where I'm trying to go with this. Posted up the first tip, The 10-Day Test, which is really all about how we organize and document our work so that others can pick it up when we're not around (like on vacation).

I'm still noodling around what I want this to look like, so bear with me and ignore the dust.