Sunday, July 17, 2016

Excel - Auto Calculate

Need to see quickly what a range of cells containing numbers add up too? You can see the results of an Average, Count, Numerical Count, Min, Max, and Sum, etc. for a range of cells without writing the formula using a tool called Auto Calculate.
Select a range of cells with your mouse that you would like calculate – for example that you want to know the Sum (total) of.
Look down in the lower right hand corner of your screen on the Status Bar. Notice that there are some calculations listed there based on your selected data.


You can customize what calculations appear on the status bar. Right-click on the status bar and then check the desired AutoCalculate choices to make them visible on the status bar.

Tuesday, July 12, 2016

Microsoft Publisher or Word

Yesterday, I taught a small group some of the basics of Microsoft Publisher. I have always preferred Publisher over Word when it comes to designing a flyer or brochure.

Most people are more familiar with Word and I often get asked why even use Publisher.
I do acknowledge that Word has gotten a lot more friendly in regards to creating documents that contain images, however, Both Microsoft Word and Publisher can help you add images and text to a document - they share a lot of common tools that make that possible. However, if you need a desktop publishing program, choose Publisher. Word excels at helping people create other types of documents. Think of Word as a powerful text editor that handles general writing tasks and Publisher as a more specialized tool to help with your creative designs.


Here are some advantages of Publisher over Word:
  • Publisher has easy to use templates for brochures, newsletters, and greeting cards.
  • You can use ruler guides to section parts of a document. This helps with the placement of objects. 
  • Precise control over positioning text with images and other design elements.
  • Easier to position textboxes next to images and wrap text around images. 
  • Poster and banner designs with easy width and height choices to match your needs.
  • Create custom page sizes.
  • Identify and correct unintended desktop, commercial print, Web, and e-mail problems using the improved Design Checker. 
  • Support for large jobs that require the services of a commercial printer, including composite CMYK PostScript output and advanced print settings for better color separations.

Tuesday, July 5, 2016

YOUR INPUT DOES MATTER (MS Excel)

Tabular Data

At my previous job as an IT Trainer, there were days when not a lot was going on. I would use this slow time to wander the halls, mainly to remind people that I was there. Software training is one of those things that most people feel they don’t have time to attend or don't need. However, if they saw me out and about, they felt comfortable asking about something they had been having problems with. That is what happened when Darlene saw me. 
Darlene had just started her new job as admin support in the department. She was still getting to know many of the documents that she had to work with.

“How weird to see you at this very moment,” said Darlene as I walked up to the service counter.
“What’s going on?” I responded.
“I need to update the motorpool spreadsheet and the formulas aren’t working,” she said as her eyes never left the monitor.

I came around the counter and she got up from her chair so I could take a seat. I clicked into various cells and then informed Darlene “the formulas are not working because there are no formulas.”
Darlene looked at me with a bit of confusion.
To clarify, I said “whoever created this spreadsheet did not setup formulas, they simply did the calculations manually and entered the result into the spreadsheet.”
“Wow,” was her response.


Wow, indeed. One of the main reasons for using Excel is to collect data. Many people keep lists in Excel. Even if you are not sure how you want to use this data or list in the future, setup your spreadsheet the right way. You will realize over time what data you want to extract. You can’t measure what you don’t track. 

Not a great way to track data

Take a look at this spreadsheet above. This is the motorpool spreadsheet that Darlene had inherited. It is for tracking what technician has checked out a car, what unit they are assigned to and how long the car is out.
What’s wrong with this?
Notice Column C (Usage), that the technician last name, hours checked out, and total hours are all in this column. Four pieces of data in one column. Notice also, that Column D and E are simply repeating the data in B and C, except it is the evening unit.

Starting in Row 15, is the Average Usage Per Unit. All of these totals are manually inputted. Not a single formula in this spreadsheet. The way this spreadsheet was structured,  Darlene could not pull any useful data out of it. 



Each piece of data now has its own column

We revamped the data. Each piece of information had its own column. We separated out the time the vehicle was checked out and checked back in. This allowed us to set up a formula in Column G to automatically calculate the total hours the vehicle was out.
With the spreadsheet reworked, Darlene could now easily filter the data. If she wanted to look at only the Day shift, then she could filter out the evening shift.
We were also able to use a SUMIF function to calculate total hours a vehicle was checked out per unit.

SUMIF function


We were also able to use a SUMIF function to calculate total hours a vehicle was checked out per unit.

No more manual calculations.

How you setup your spreadsheet really does matter.