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.

Advertisements

Excel: 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.

Excel: Quickly adding more than one row or column

When working with spreadsheets, we often find that we need to insert a column or row into a document. The way we do that is by clicking on the column or row labels (column letters or row numbers), and using either right-click or the ribbon commands to add a column or row.

If you need to insert multiple columns and multiple rows, doing so one at a time can waste quite a bit of time.

To add more rows, click on the row label at the row below the spot where you want to insert rows.

Then select more rows by holding down the shift-key and using the down arrow, or by clicking on the nth row, and then choosing your regular insert method. This will allow you to insert more than one row, at exactly the spot you want.

For columns, the procedure is similar: click on the column label to the right of where the new column should appear. Use the shift-key and right arrow, or click on the nth column, and then use your regular insert method.

When I first introduce this to people they are sometimes apprehensive that the data in the selected rows or columns will be replaced or deleted, but rest assured, the data just moves.

Excel: what does the dollar sign do?

Oftentimes when working with Excel, you’ll notice that dollar signs ($) appear in front of, or in the middle of cell references.

For example:
$A1
A$1
$A$1

What does Excel do with this information and why do you need it?

It means that the row or column which comes after the dollar sign is anchored or absolute, and therefore will stay the same as you copy and paste formulae to another cell.

Excel by default uses “relative” reference to a cell, which means that the formula changes as you copy it to another cell.

For example, a formula in column D that states =SUM(A1:A5) will become =SUM(B1:B5) when it is copied into a cell in column E.

By using the dollar sign, you can ensure that either the row stays the same, the column stays the same, or that you want a specific cell referenced.

So using the references from the above example:

$A1 means that the formula will always pull information from column A, regardless of which column it appears, but it can change the row depending on where the formula appears.

A$1 means that the formula will always pull from the first row, regardless of which row it appears, but it can change the column depending on where the formula appears.

$A$1 means that no matter where the formula is placed on the sheet, it will always reference the A1 cell.

Why do we care?

We generally care if we’re trying to reference information in a list. We don’t want the number of items changing in the list, so if you have information in rows 1 through 20, then you don’t want the first row to have a formula looking at rows 1:20, and the second row to have a formula looking at rows 2:21, etc. You would want to make sure your range at least has dollar signs in front of the row numbers, so that A$1:A$20 will always pull rows 1:20 from the column.

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.

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.

Effects of changing Office365 login – part 8 (conclusion): Other Office Applications

This post will conclude an 8-part series on the consequences of changing the login for Office 365. Overall, the effects of all these changes were fairly minor, partly due to the upfront communication everyone received, and partly because we still were not heavy users. Our IT people were on hand to get the sync clients up and running, and while I did need to help a few folks with OneNote anomalies, very few people had issues reconnecting their OneNote files. It also helped that SharePoint files were pretty much unaffected, which was another reason why we push for the use of SharePoint rather than OneDrive for our division.

Here are some other notes we found when working within the Desktop Office Suite, for your education:

Effects on Other Office Applications

If you receive a warning message while working on a document indicating that the sync is failing or asking you to sign, do the following.

  1. Check the account used by the application by going to File/Account. If “PID@w2k.starfleet.com” is being used, then sign into “PID@starfleet.com”.
  2. Now click “save as” and browse to appropriate location in your Microsoft OneDrive
  3. Locate the document you are working on
  4. Click “save” and application will update the path of saved document to match your new account
  5. If you are concerned that you have changes that may be lost, you can save the document to new location, confirm your changes are present, and then delete the older file