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.

Removing all images in Word or Excel

Sometimes when copying information from a SharePoint page (or any website for that matter) to an Excel or Word document, you can end up with images that you don’t want. You could click each of the images and hit DELETE to get rid of them, but here is a faster way:

In Excel

Use the Go To command (Ctrl + G), then choose Special, then Objects.

The delete and backspace keys will remove the images from your worksheet.

In Word

Open the Find & Replace tool (on the PC, it’s Ctrl+H)

To remove all images at once, place ^g in the find field, leave the replace field blank, and then choose Replace All

Excel: COUNTA, COUNTBLANK

Whereas COUNT counts only the cells that have numbers in them, COUNTA and COUNTBLANK are a little more loose with the definition of what they count. They count all cells and report on the information.

COUNTA

COUNTA counts cells that are not blank.

You might use blank spaces to create a view that separates certain types of data so that it’s easier on the eye, such as the T-shirt example below.

However, if you want to know the total number of sizes and colors, you don’t want to count those spaces.

In that case, you would use =COUNTA(B2:B18), which would give you a number of 15

Excel: COUNTA example

COUNTBLANK

COUNTBLANK does exactly what you would expect it to do: it counts the number of blank cells.

=COUNTBLANK(A1:C18) gives me 6 because I have 6 blank cells.