Microsoft Office: Hyperlink Window

Ctrl + K opens up the Hyperlink window in a number of Microsoft Office suite applications. Another way to get to the Hyperlink window, is to right-click your text, showing the menu. A third way is to use the INSERT ribbon and click on LINK.

Inserting a link activates the “Insert Hyperlink” window box, which looks a little different based on which program you’re in.

I don’t know if I have ever really examined this Insert Hyperlink window, because I usually know where I need to go, which is usually somewhere on the machine in which case I would use the folder window below to browse, or a web address which I would normally have copied from the internet and pasted in the Address bar.

Microsoft Office: Hyperlink Window

But, take a look at this window and all the options it offers: Existing File or Webpage, under which you have Current Folder, Browsed Pages, or Recent Files.

Current folder shows the above view.

Browsed pages looks as below:

Microsoft Office: Hyperlink - Browsed Pages

Recent Files looks as below:

Microsoft Office: Hyperlink - Recent Files

Beyond Existing File or Web Page there are three tabs in the Hyperlink window: Place in This Document, Create New Document, and Email Address, which we will look at more closely in the next post.

Make the same change across multiple worksheets

In working on the Resident Adviser Duty Schedule spreadsheets for Housing and Residence Life, we changed the procedure a little bit. Instead of having 20 individual spreadsheet files with 10 sheets in them for each of the months, we made 10 monthly spreadsheets with 20+ building sheets in them. It allows us to make changes to one master file for the month, and it is referenced by all the building sheets. Using Office 365 we can have 20+ users making schedule updates at the same time without locking the sheet.

You can imagine that formatting and copying and pasting formulae across 20 sheets could be a nightmare, but Excel has that covered for us, and has a way for us to make changes across multiple sheets.

Making changes in multiple sheets

You can select multiple sheets at one time by holding down the Ctrl key as you select the sheets you need to modify. Making a change on the current sheet will then replicate across them all.

Excel: Select Multiple Sheets

If you want to change all the sheets at once then right click one of the sheet tabs and choose Select All Sheets.

Excel: Select All Sheets

Using some of these features in Excel can save us an immense amount of time. Let me know in the comments which features you use most often.

Auto-resizing a cell in Excel

When you work in Excel, the default width for a cell column is 8.11 and the default height for a cell is 14.4. When looking up where those measurements came from, there doesn’t seem to be rhyme nor reason. The default column width has to do with default font size, and 8.11 of a standard character fit in the column. Who knew? The row height apparently has to do with the number of pixels, and again, seems somewhat arbitrary, but there you have it.

So why do we care? Because while we appreciate the uniformity of the grid system, not everything we do fits into these 8.11×14.4 cells, and we sometimes need to make them bigger or smaller, depending on our needs.

What is your preferred way of resizing a cell?

Do you click in between cells until you get a cross hair and then drag it to fit?

Excel: Column Width Slider

If you double-click in the same spot where that cross hair appears, the cell will Autofit to accommodate the widest or tallest content the row or column.

Excel: Column Width - Wider

Another way to get rows and columns to Auto resize, is to use the FORMAT | AUTOFIT option from the HOME ribbon bar.

Excel: Format | Autofit

I hope this helps you in your Excel journey, and that it saves you some time. Note: You cannot use Autofit feature for rows and columns that contain merged cells in Excel.

Bonus Tip from Microsoft: if you manually modify the height of a row, and then later wrap text in a cell in that row, Excel will not auto-adjust the height of that row to fit. Essentially, it figures you made the choice to make it that height, and so it’s not changing it unless you tell it to.

When this happens, you will need to select the row you want to adjust, then click AutoFit Row Height from the Format button in the HOME ribbon.

Excel: select all cells with one click

Excel is a powerful tool that will let you do just about anything with data. I use it all the time to parse data, and take a lot of features for granted.

I happened to be sitting with someone working with Excel and they wanted to select the whole spreadsheet. I showed them they can click on this little box, and it felt like I had just told them they won the lottery. It was like Christmas.

I love it when I can make someone’s day, so here’s hoping I can make your day as well.

Between column A and row 1 there is a little box, which contains a triangle in the corner. Clicking in that little box will select ALL the cells. You can accomplish the same thing with short-cut keys Ctrl + A.

Excel - select all using the mouse

I use this feature often when there are format changes I want to make across all cells. For example, when I want to resize all of the rows, or if I want to make all the font settings the same for the whole spreadsheet.

Excel: Select columns or rows in their entirety

When you need to select a whole column, click on the column letter, and it will highlight the column. You can then manipulate the content or the formatting of that cell.

Excel: column selected

To select a whole row, click on the row number and it will highlight the row so you can make adjustments to fonts, etc.

Selecting Rows and Columns using Spacebar Shortcut

I also recently learned about a keyboard shortcut involving the Spacebar.

Ctrl + Spacebar selects the entire column

Shift + Spacebar selects the entire row

Using these shortcuts will save us from having to highlight and drag our way through a long worksheet.

Use Dock to Desktop to keep your note on the forefront

Docked notes allows you to keep your note front-and-center while you work.

Using the docked notes, I can research and review what I am doing, or keep notes of the steps of a process.

I often use the Windows + Arrow keys to split the screen in two so I can view OneNote and the browser side by side, but using the docked notes option removes all the application clutter that comes with that.

To show what that looks like, here are the screen shots for both options. You can see how using a docked note can be a much cleaner way of working in your OneNote.

To activate the docked notes option:

  • Go to the View tab
  • Choose Dock to Desktop from the Views section

OneNote: Dock to Desktop Option

Example of “side by side” view

OneNote - Side by Side View

Example of “docked to desktop” view

OneNote: Docked Note

The docked note stays in the same place, no matter which applications you’re using elsewhere on the screen so it saves quite a bit of time switching back and forth between OneNote and other programs.

Changing Paper Sizes in OneNote

Microsoft OneNote notes are created with default sizing, but you can adjust this:

  • Go to View tab
  • Choose Paper Size from the page setup section
  • On the right size of the screen, a menu will pop up with various options.

OneNote Paper Size Dialog Box

If you save the current page as a template, then you’ll be able to set that template as the default when creating a new page, so if you want to have a recipe book with index card sized notes, then you can set the Index Card as the default template and any new page you create will look like an index card.

OneNote: Paper Size Index Card

Another place this might be helpful is if you use your tablet or smartphone with OneNote on a regular basis, you can set the width of your note to match that of your smartphone and you’ll always be working within the confines of that space.

For the most part, this is not necessary: if you’re using one text box, the text will automatically wrap, but if you’re using multiple text or graphic boxes, the info won’t automatically wrap and the page will be as large as it is on the desktop. Having the pages sized to the smartphone ensures it will always be accessible to you when using that device.

OneNote: Paper Size - Postcard