Microsoft Office Tips & Tricks

Project Tracking in Google Docs (and some cool new spreadsheet features)

2013-11-17T22:22:00.001-05:00

http://projectmanagement.informationloop.com/2013/11/tracking-project-time-in-google-docs.html

I had a recent need to find a new method of doing time reporting against projects and services. My services have different resource commitments, and its important that we show our customers and stakeholders that we are fairly delivering value. Before you make the normal sigh, and under your breath mumble what a waste of time... hear me out!

Why does it add value? Let's go to the root of the reason why we track time. Is it to hold our staff accountable for punching a clock? No, if you evaluate technology professionals on how much time they spend in front of a keyboard typing, shame on you. You skills are dated. You should judge a person's contribution based on the value they deliver. Would you pay a mechanic that looks busy for four hours $600 for a basic car inspection when everything passes and no repairs are necessary? No, obviously not. You want some type of value. Judge your information technology by the same litmus. What do I mean? I honestly prefer my staff to spend the same time researching a problem and resolving it with a few lines of code that resolves the root issue... than develop a thousand lines of code that fixes the problem by masking the error. The extra code just adds future maintenance costs.

Ok, I'm digressing... let's get back to the point...what is the purpose, really?

Tracking time against project and being OBJECTIVE can provide the data you need to improve your project management skills. Are there areas of your projects where you consistently are optimistic and end up behind schedule?
Could it be you need to add a weighting factor... since everything never goes well... and everyone plans for the most optimistic plan... or the most pessimistic plan... instead of the most realistic plan? You learn that factor from looking at your projects, and how well you've met the time schedules.
Do you have one resource that consistently can't meet the time schedules, when others continually are on-time? You may have a training issue that you need to address... or you could have someone who is getting all of the more difficult work? You only know by looking at results.
What is your actual non-project time? You build a schedule based on programming time... how much additional time is spent in meetings, on phone calls, walking through unexpected use cases, out sick, training, vacation, etc.? Tracking helps you figure this out. I reallocate non-project hours against project hours to fully absorb these so I can tell my customers how much time was spent on each of their services. This technique comes from my manufacturing background.
Don't be an idiot. If you track time at an extremely high level to keep things simple, and can't tell other than looking at remaining hours where your project is at... you're not planning correctly. If you can't identify a critical path across the project... other than "TASK A"... you could use some training. I've blogged about this before... check it out.

Now, the solution for a small team. I'm not sure how well this scales, so keep this in mind...

I've established a Google Doc. The first parts' pretty simple... I have one week for each person to enter their time against project. Now, for a little more advanced techniques... I want to be able to summarize by service, project, and task. Since I have a smaller team (less than 10), I often have people working together on a project or service. I need all of the summation to be correct and consistent, and it's unfair to ask everyone to type in the service, project, and task names identically. So I've created a list of projects that my staff and I can maintain as a lookup. Also, as a quick trick - you'll see red cells on the sheet that say "insert between here". This is so total areas grow automatically to include new data when things are between the red lines. You'll see what I mean... my formulas don't go from the first to the last number that should be added... they go to the red cells instead (Check out Fannie's tab in the Google Doc example below).

Here's a few more tricks for you:

Data validation - you can create a tab that has a list of valid choices so that all of your projects must be from the master list. Check out Fannie's tab for the projects she reported time against... or try it yourself using the menu selection Data -> Validation.
VMERGE - what a cool llittle script - VMERGE allows you to get pull from multiple sheets, and put it in a single tab so you can analyze it. You activate the script by going to Insert -> Scripts... and then type in VMerge. You can also check out the linked documentation. For a larger team, you may want to investigate the importrange function to see how well it scales.
Query function. I've used SELECT, SUM, and GROUP-BY through-out the spreadsheet. It's very SQL-Like - I opted to use column letters so as my sheets grow, I don't need to redo the formulas.
Where-ever it makes sense, Google Docs appears to allow columns instead of cell ranges. This adds flexibility. Check it out for yourself.
PivotTables - they are a great way to portray information as long as they can be predicted or controlled (rows vs. columns). In my sheet, I opted for the most part to stay away from these... since my projects were limited and I wanted more granular control of my summary data.
Conditional formatting - Right click on selected cells - and select conditional formatting. In my case, under 40 hours has a red background... and over forty has a green background. Again.. check out the formulas within the totals on Fannie's page.

So, what does it look like?

Employee entry:
Employee summary:

Summary report:

I've posted the generic spreadsheet so you can check it out... https://docs.google.com/spreadsheet/ccc?key=0Ag8WlpBJykv0dHdOZF9zN1FtelJLWTlaN1FKalNDd2c&usp=sharing

I'll cross post this on my excel blog. Also, if you find you have some trouble with your projects, and you want to learn more... check out my prior posts. Sure, there's some dry humor... what do you expect? I usually write this stuff late at night when I can't sleep!!!

If you have specific questions, leave a comment, and I'll get back to you!

Thanks!

Compressing Pictures in Microsoft Outlook 2007

2013-01-30T21:12:00.001-05:00

We've all been there... and it's becoming more of an issue. Grandma, mom, or "that" user complains about how long it takes to send pictures - and it must be the computer... the internet... the antivirus... the conspiracy that the technology companies have created so they have to purchase a new router... updated PC... faster DSL or Cable... yes... you know THEM!!! Who knows, you could be one of THEM and never even knew it!!!

I must add some basic caveats... try this at your own risk - my posts were created by an idiot... my wife can confirm that fact. I'm not responsible if I messed up the directions, or if you are incapable of following them, or even if you do follow them correctly, and something bad happens. Always validate and verify any instructions you receive. That's good advice for life, and technology. I always test any techniques I learn on something of no value I would normally discard anyway.

I've placed some general instructions to optimize the picture below. I haven't tried this with other versions of Microsoft Office / Outlook to verify it works. However, I suspect it does. When I have a chance, I'll verify and post it later.

The general steps for the example.

In Microsoft Outlook 2007, create a new mail message.
You can either attach pictures to the message by using the "attach file" paperclip, or by using Windows Explorer and selecting a group of photos using your preferred "copy" technique.
After you use the paperclip, or the "paste" technique, you end up with something like the image below.
Once you have attachments, you click the drop down arrow I've highlighted in Step #2 below (on the Message tab on Outlook).
You can decide your level of compression using the drop down dialogue box on the right panel. If you decide this method works, then you can click the bottom checkbox a shown in Step #4 below so the dialogue appears every time you attach photos.
Finish your email, and hit send.

You should try different resolutions and send them to yourself to see what works best for your requirements.

In the case of the 20 5MB photos listed, they zipped down to a little under 900K. That's great for sharing. It won't work if Grandma wants to print a 11x14 photo of one of the pictures. This technique is really meant to compress pictures for viewing pleasure, not for printing. Use your own common sense, and testing to ensure that whatever you compress still meets your future needs and requirements. Also wouldn't be a good technique for a wedding photographer to send prints to his developing studio.

I hope this helps!

Happy Computing!

So you think you know MS Office?

2011-05-30T01:10:00.001-04:00

So, you think you know Microsoft Office? I'll be posting some videos to show you some things that may not be real familiar to you. I've been using office...well... for years.

Over the years, I've used the following office predecessors... Lotus 1-2-3, AmiPro, Twin, Quattro, WordPerfect, Quattro Pro, WordPerfect Office, and Microsoft Office --- all versions since the beginning. I'll also touch on Databases, and Presentation Software as well.

Here's a few thinks I'm thinking of videoing... links to be added later...

Excel - formatting, advanced filling, pivoting data, outlining series, advanced macros, graphs, etc.
Word - just the basics - columns, headers, footers, styles, interoperability, etc.
PowerPoint - adding pizazz like transitions, animations, meaningful data, and good presentation skills.
Access - basic data and forms entry, validation, lookups, reports, advanced queries, etc.
Advanced SQL - you know you want to learn more about this... set tight and I'll see if I can help you.

That's it for tonight. I'm just getting everything setup for now.