TechBits

Creating simple subtotals in Excel

Most people who work with Excel spreadsheets know you can total a group of numbers by using the Sum function.  But did you know Excel has a subtotal function that will create simple subtotals and a grand total for you? 

Open the spreadsheet and select the data you would like to subtotal.  Then go to the Data tab and look on the right hand side for the Subtotal option.  Click on the Subtotal icon and a pop-up window will appear asking how you want to subtotal the information. 

The “At each change in:” option, lets you define the groups of entries you wish to subtotal.  For example, if you have a report that is for all libraries, you can choose to have it subtotaled by library by choosing Library for the “At each change in:” option.  To know what’s available for options, click the down arrow on the right side of the in the “At each change in:” box, and choose from a list of possible selections. 

The “Use function” section allows you to choose what kind of subtotal you wish to see.  For example, you can go with a straight up sum of the numbers or you could choose to show the average of the numbers instead.  Again, to know what’s available, click on the down arrow at the end of the “Use function:” box.

With the “Add subtotal to:” selections, you decide which column is getting subtotaled.  Most of the time you’re going to want to choose a numeric column since subtotals of most non-numeric data don’t make much sense. 

There are also three checkboxes for subtotal options.  First is “Replace current subtotals”.  If you are wanting to do multiple subtotals, for example one for the sum and one for the average, deselect this option for the successive subtotals to keep them from replacing the original subtotals.  Next is “Page break between groups”.  This will cause each subtotaled group to print on a separate page when you print the spreadsheet.  The last option is “Summary below data”.  With this, you control whether the grand total and the subtotals are at the top of what they’re totaling or if the subtotals come after the list of entries being totaled. 

When you're done with your selection, click on OK.  You've now got a quick, simple subtotal of your data.

Header and footer fun (aka "where does that document live?")

I wanted to add a footer to my Word document that would automatically display the name of the document and the path to the document. I knew how to add a footer, but couldn't figure out how to make the file and path information appear without me typing it... so I looked it up. Here's the answer:

Click on images to view full-size

1. Insert tab -> Footer 
   (I chose the "Blank" option)
2. Insert tab -> Quick Parts -> Field...
3. Select "FileName" and check the box to "Add path to filename"
4. Click on the Design tab and click "Close Header and Footer" to finish

Easy peasy. And it's the same basic idea when working with headers.

Another helpful option might be to add the "SaveDate" and "LastSavedBy" fields to easily know when and by whom the document was last updated.

Which of the "Quick Parts" fields do you use in your documents?

Excel reports and library barcodes

Have you ever opened an ILS report file and seen some data like the mess shown here? I think we all know that valid item and patron barcodes are longer than these, and that they don't have "E+13" in them.

Of course you may not have ever seen that, because SCLS staff do try to clean up such things for you. But sooner or later we may miss a spot. More likely, you'll get this kind of result on your own while playing in whatever kind of do-it-yourself reports tools get added onto ILS services over time.

No worries, it's just Excel being Excel. It sees a number with lots of digits (twelve or more), and it decides to show some initiative and help you out. It automatically converts those long numbers into a "scientfic notation" format. Because that's better, right?

Yeah... not so much, really. Luckily this is easy to correct:

 1. Select the columns or range of cells you want to fix.
 2. Right click inside that area and choose "Format Cells..."
 3. Choose the "Number" format category.
 4. Reduce the value of the "Decimal places:" field to zero.
 5. Click OK.

Wallah! There's your barcodes, all shiny and new like Excel never made hamburger out of them at all.

Pinning Files and Folders in Office

The Recent Items or My Recent Documents feature in Windows is a handy way to quickly pull up documents you've been working on recently.  The same goes for the Recent document list in Word.  But what if you're working with a lot of files?  The "recent" lists are ever-changing and the file you're looking for might not be on the list for very long.

Micrsoft Office has a feature that allows you to "pin" files and locations to the Recent lists to make sure they are always available on the list.  Office 2007 allows you to pin files and Office 2010 allows you to pin both files and places, aka recently used folders. 

Click on File from the menu bar and then click on Recent.  Your current "recent" files and locations will now appear.  After each file or folder name is a grey sideways pushpin icon.  Click on the pushpin icon once to pin the file or folder to the list.  If you no longer need that file or folder pinned, just click on the now blue "pushed in" pushpin icon and the file or folder will be unpinned.

Just remember that each file or folder you pin means one less unpinned file or folder that will show up in your Recent list in Office. 

Sum it up with a shortcut

Another handy Excel shortcut...  Alt =

To sum a column of numbers, select the cell at the bottom of the column. Type Alt = to insert the sum function.

Filter it

I've spent a fair amount of time recently looking at database statistics and spreadsheets. I had a great question yesterday that had me filtering these spreadsheets to help make the data a little easier to read.

The question was, "Is there an easy way for me to see subscribing libraries' use of Morningstar and ValueLine?"

The answer is "Yes!"  We offer the database stats in spreadsheet format. You can filter any spreadsheet to show only certain data.

Here's how you would tackle this particular example in Excel 2007:

• Take your spreadsheet (I'll use the 2011 Remote Database Use spreadsheet in my example)
• Ctrl-A to select all of the data on a sheet
• Click on the Sort&Filter button
• Choose Filter
• In the spreadsheet, click on the drop-down arrow for the "Database" column
• Uncheck the "Select All" box
• Check the boxes for the databases whose stats you'd like to view (Morningstar and ValueLine for this example)
• Ta-da! Remote Use data only for Morningstar and ValueLine!

(Click on the pictures to see them full-size)

 

Freezing panes in Excel

I made peace with a monster spreadsheet recently with this tip to freeze the header row and column at the same time to keep them both visible while scrolling.

Place the cursor in a cell below and to the right of the row and column you want to freeze, then select View > Freeze Panes > Freeze Panes.

The header row and column stay frozen on screen alongside those far out cells after scrolling.

Thanks to Kerri for teaching me this tip just when I needed it!

Zoom, zoom, zoooooooooom

Zoom in MS Office
Did you know you can quickly zoom in or out of a document (Word), presentation (PowerPoint), or worksheet (Excel)? 

 

• On the status bar in the lower right, click the Zoom slider
• Slide to the percentage zoom setting that you want

 
Zoom in IE 

Want to quickly zoom in IE?
<--- There's a zoom menu on the status bar...

And if you prefer keyboard commands, don't forget

Ctrl +  (zoom in)

Ctrl -  (zoom out)

They work in most browsers!

How to use autocomplete in Word 2007

Do you get tired of typing the same string of characters over and over in a Word document?  For instance, since I work for the South Central Library System I may have to type that several times in a document or several documents. Autocomplete is turned off by default in Office 2007. Here are the steps I have used to get around that.

 In Word highlight the string of text you wish to have autocomplete. Press Alt F3, that will bring up the "Create New Building Block" dialog box.

Fill out the information in the Create New Building Block dialog box:

Name: This is filled out automatically with the text you highlighted.

Gallery: Select the gallery that you want the building block to show up in.

Category: Select a category, or create a new category.

Description: Type a description of the building block.

Save in: Click the name of the template in the drop-down list. A template must be open to be displayed in the drop-down list of template names. I use the default "Building Blocks.dotx".

Options: Choose one of the following:

    Select Insert content only. This is selected by default and is the one I use.

    Select Insert content in its own page.

    Select Insert content in its own paragraph.   

 That's how you create an Autocomplete template. To use it start typing your string of characters, usually you only need the first two to four to start and hit the F3 key and the rest will autocomplete. This will work only if there are no other entries that begin with the same four letters, so be aware of that.



Learn Free

GCFLearnFree.org is supported by the Goodwill Community Foundation.  According to their website, they "create and provide quality, innovative online learning opportunities for anyone who wants to improve the technology, literacy, and math skills necessary for them to be successful in both work and life."

What will you find there?  Tutorials.  Lots and lots of tutorials, some videos, and even online classes!

Here's a peek at the main technology topics (click on the image to enlarge it)...

There are tutorials for multiple versions of the Office products -- 2000 all the way up through 2010.  Social Media offerings include Facebook, Skype, Twitter, and Google.

I haven't run through any of the tutorials yet, but I thought they might be a nice possibility for patron training.  Each of the many lessons also offers a printable version.  As an example, here are links to the lesson and the printable version of the lesson on Mail Merge for Word 2007.