Some useful Excel keyboard shortcuts

At the "Advanced Microsoft Excel Techniques" class, that I recently attended the instructor told us something that I never realized before.  What he told us was that it was faster to use keyboard shortcuts in Excel than to use the mouse.  His reasoning was that if your fingers are already on the keyboard that you waste time moving them over to the mouse.  Since he loved keyboard shortcuts so much he gave us a lot of them in class.  I thought I would share some of them with you to help you save time, which I'm sure we all like to do.

Moving Around

  • Ctrl-PageUp/PageDown: Move to another worksheet
  • Ctrl-Down/Up Arrow: Moves to the top or bottom cell of the current column that contains data
  • Ctrl-Left/Right Arrow: Moves to the cell furthest left or right in the current row that contains data
  • Ctrl-Home: Moves to the beginning of the worksheet (cell A1)
  • Ctrl-End: Moves to the end of the worksheet (last cell with data in it)

Data Selection

  • Ctrl-Spacebar: Select entire column of the active cell
  • Shift-Spacebar: Select entire row of the active cell
  • Ctrl-Shift-Down/Up Arrow: Selects all the cells above or below the active cell
  • Ctrl-Shift-Left/Right Arrow: Selects all the cells to left of or to right of the active cell
  • Ctrl-Shift-Home: Selects from the active cell to the beginning of the worksheet (cell A1)
  • Ctrl-Shift-End: Selects from the active cell to the end of the worksheet (last cell with data in it)
  • Ctrl-a: Selects the entire worksheet; only those cells that contain data

Formatting

  • Ctrl-Shift-!: Format selected cells to have two decimal points
  • Ctrl-Shift-$: Format selected cells to currency
  • Ctrl-Shift-%: Format selected cells to percentage
  • Ctrl-Shift-#: Format selected cells to date
  • Ctrl-Shift-@: Format selected cells to time

Miscellaneous

  • F2: Opens the active cell for editing in the formula bar
  • Alt-=: Autosums the cells above the active cell

There are many, many more keyboard shortcuts for Excel.  This was just some of the ones that I use and I hope this piqued your interest to go searching to see what other ones are available.  So the next time your mouse stops working you'll still be able to work on your Excel spreadsheets.

More to Paste Special than Values

Pastespecial

I was working with a spreadsheet and needed to convert some numbers from negative to positive.  While I knew I could add another  column with a formula to multiply each of the numbers by -1 and then copy and use paste special: values to paste the results back into the spreadsheet or I could select the column of numbers and do a find and replace to replace the – with nothing, I decided to see if Excel had a quick widget that would do the work for me.

I didn’t find a quick “switch from positive to negative” button but I did find a different way to go about changing the numbers.  

  1. Put -1 in one of the Excel cells.
  2. Copy that cell.
  3. Select the numbers you want to convert.
  4. Right click and choose Paste Special. 
  5. In the middle of the Paste Special window, there’s a section called Operation.  Select Multiply and click on Ok.  Your numbers have now been switched.

I’d seen the Operation choices before but, frankly, I’d ignored them. Until now I’ve always wanted one of the choices from the upper section (Formulas, Values, Formats, etc.) so I’d never really looked at any of the Operation (Add, Subtract, Multiply and Divide) options.

Now while a simple find and replace would have worked for my original problem, something like this could really come in handy for other situations. For example, if I needed to subtract 10% off of a set of numbers, I could put .9 in the cell instead of -1 and I'd get the numbers without having to insert a column and create a formula.

Website editing tips: Shift+Enter and Paste as Plain Text

These two website editing tips got such a positive reaction at the latest Drupal Basics website training session that I want to shout them from the rooftops:

Shift+Enter is the keyboard shortcut for "Make a line break without starting a new paragraph and adding a margin of space between lines," like so:

Shift-enter

This tip does double-duty: it works in the editor SCLS provides for Drupal websites, and it works in Microsoft Word. This and more keyboard shortcuts are in the Glorious Guide to Keyboard Shortcuts (which was written for Drupal sites hosted by SCLS, but most of the shortcuts are standard across many apps).

Paste as Plain Text (Paste-as-plain-text) is the editing toolbar button to use when you copy a chunk of text and want to paste it into your SCLS-hosted Drupal website without dubious font/size/color formatting. The button opens a popup dialog, and you just hit Ctrl+V to paste your text in. Once pasted, the text will lose all its formatting and you'll be able to reformat it using the toolbar options. It's not just for brand new pages, either—you can also copy existing website content where you have been fighting with troublesome formatting, and paste it in as plain text to start fresh. Also good to know: Remove Format and Source buttons and Ctrl+Shift+V.

Sort by Subtotal

If you try to sort an Excel spreadsheet that has subtotals, Excel gives you a "This removes the subtotals and sorts again..." message.  This is fine if you want to sort the underlying rows differently, but what if you want to sort by the subtotals themselves?

To do that, you need to collapse the display so only the subtotals are showing and then do a sort.  This will sort by the subtotal, but not the underlying rows. To collapse the spreadsheet so only the subtotals show, go to the subtotal levels that display on the left hand side of the spreadsheet and click the number at the top that corresponds to the subtotal level that you want to use to sort.  The number may change depending on how complex your spreadsheet is, but in general level 1 shows only the grand total and the last level on the right shows all details.  You may need to experiment to figure out which level is the one you need to use.  In this case, it's a simple subtotal so the level is 2. 

Subtotal-full

This collapses the spreadsheet so only the subtotals are showing.  When the subtotals are collapsed, you'll see a + sign in the box to the left of the subtotal instead of the - sign. 

Subtotal-collapsed

 

Once everything is sorted by the subtotal, you can sort as normal.  If you want to see the details for all of the subtotals again, click on the level button on the right hand side.  In this case, it would be level 3.

Annotating Screen Shots with Word "Shapes"

This week I had the need to annotate some screen shots. I haven't had much success with previous tools I tried.  My husband told me about the Shapes feature in Microsoft Office Word. From Word, access Shapes from the Insert tab. I used one of the Callouts which provided me with a text box where I could include as much text as I liked. The shape I selected (Line Callout 2) had a line that I could move around to any point in the screenshot--you can even make it longer or shorter. Finally, you are able to pick the thickness and color of the shape outline and you can even shade the box. From now on I will be a screen-shot editing pro!

Shapes (2)

 

Excel - Remove duplicates

Imagine you have a list that contains duplicates, and you want to delete the duplicates. No need to do this manually --- let's use Excel's "Remove duplicates" feature!

Click on the image to see it full-size
Removeduplicates

This is one of my favorite TechBits pictures. I wish there were 3 of me to do my work!
Duplicate Craigs!

Note: Another option to get rid of the duplicates is to filter for unique values. Filtering temporarily hides duplicate values, where removing duplicate values permanently deletes the duplicate values. See this Microsoft help page for more information about both options.

When are your internet computers the busiest?

If you use MyPC for time management (as many of our libraries do), there is an easy way to see when the PCs are busiest... and exactly how busy they are. 

  1. Log into MyPC with your staff credentials.
  2. Reports > MyPC Reports > Utilisation Reports > Peak Usage Report.
  3. Click Modify.
  4. Unselect the _default site. Select your library (all locations). Update.
  5. Select your desired Time Period.
  6. View Report.

MCM Peak Usage Report

 

Bonus tip Heat map - all steps

 

In no time at all, you can easily pull the report data into Excel and use Greg's previous TechBits tip to color code the data to see the busiest times at a glance.

 

Click on the image to see the steps.

More incrementing in Excel

In a previous post, we showed you how to "be a lazy incrementer in Excel" by clicking and dragging the fill handle.

In addition to incrementing numbers, you can also easily increment dates or repeat a list. Here are some examples:

Dates
In this case, I entered dates for two weeks. When I select those two cells, Excel looks at them, determines the pattern, and when I click and drag the fill handle (that little black box), increments to fill in the rest!
Weeklydates 

A repeating list
Let's say you have a repeating schedule for taking out the trash...
Beatles

Sometimes Excel guesses at the pattern but doesn't quite get it right...
Every2days

In this case, it guessed that Monday, Wednesday, Friday meant that we were incrementing by two days, but really I'd like to keep repeating the three days. To do this, I have two options:

  1. Hold the Ctrl key down while I click and drag. Excel then repeats the pattern instead of incrementing by two days.

    -OR-

  2. Enter Monday, Wednesday, Friday, Monday to make the pattern clear. Then click and drag.
    MonWedFriMon

 

 

Instant Excel Charts

Bps-chart-sliceIt's annual reports season again, and I know you're all as pumped as I am about sifting through all those numbers.

But how do we extract real meaning from these data? Charts can help us visualize the key similarities and differences between data points, but can also be tedious. Here are a few tips for greasing the wheels in Excel.

First, know your shortcuts. Pressing F11 will instantly insert a new tab containing Excel's best guess for what you might want a chart of, based on what cells are currently selected. Sometimes it guesses wrong, but mostly it does pretty well. For most SCLS report products, if you have cell A1 selected then Excel will typically do the right thing when you press F11. Pressing Alt-F1 has a similar effect except that Excel will embed the new chart alongside (or more likely, on top of) your source data, instead of in a new tab.

Second, know that you can change Excel's behavior for both of those keyboard shortcuts. In either case, Excel will be inserting the "default chart type". The default default is a simple column bar chart. If that's not what you want, you can easily change the default type to a line, pie or other chart type. To do that:

  1. Select any spreadsheet cell.
  2. Click the Insert tab of the Office Ribbon.
  3. Click the Other Charts button (near the middle of the Ribbon).
  4. Choose All Chart Types... at the bottom of the menu that appears.
  5. Click on the chart type that you want to be the default type.
  6. Click the Set as Default Chart button.
  7. Click Cancel (so as to not actually insert a chart).

You may find that none of the built-in Excel chart types are perfect. If you go to the trouble of manually tweaking their colors, fonts, layout, etc., you can then save that work as a Template, then make that template the default chart type. After doing that, F11 means "insert your perfect chart".

Seeing the unseen

Ever find yourself fighting with formatting in Microsoft Word and wishing you could see what's going on behind the scenes? Enter "Show formatting."

Recently, I had a question from a library about why their Acceptable Use Policy was formatted so funny when it displayed in MyPC. It turns out there were some characters in their .rtf document that they couldn't see in Microsoft Word, but which were causing some trouble when the MyPC software tried to interpret the document.

Here's how we fixed it:

  1. We opened the .rtf document in Microsoft Word
  2. We followed these instructions to "Show/hide formatting marks"

    Show-HideFormatting
    Formatting characters
  3. We could then see that there were some extra line breaks and paragraph marks that shouldn't have been there, and we manually deleted them.