Changing the Default Vertical Alignment

by Allen Wyatt
(last updated November 13, 2021)

8

When David adds information to a cell, the default vertical alignment is "bottom." He wonders if there is any way to change the default for the vertical alignment so he doesn't need to make this change every single time he uses Excel.

The easiest way to do this is to create a default template for your workbooks. Here are the steps:

  1. Open a new Excel workbook.
  2. Make sure the workbook has as many worksheets as you want available when you subsequently create a new workbook.
  3. Change the vertical alignment for all the cells in every worksheet in the workbook.
  4. Make any other desired "default" changes in the workbook. This may include (but not be limited to) margins, headers, footers, column width, row height, and the like.
  5. Choose Save As from the File menu, or simply press F12. Excel displays the Save As dialog box.
  6. In the Save As Type pull-down list at the bottom of the dialog box, select Excel Template or Excel Macro-Enabled Template. (The option you select depends on whether you have macros in the workbook or not.)
  7. The file name you use should be Book.xltx or Book.xltm (depending, again, on whether it contains macros).
  8. Save your newly created template in the XLStart folder. (Do not save it in the default template folder.)

That's it. Now, any time you create a new workbook, Excel uses your template (Book.xltx or Book.xltm) as its model for what you want. This means your cells will have the desired vertical alignment, as you defined in step 3.

If you are unsure of where the XLStart folder is located (step 8), use Windows to search for the folder. Its exact location can vary depending on how Excel was installed on your machine, as well as the version you are using. That being said, for most modern versions of Excel, this is the default path:

C:\Users\"your name"\AppData\Roaming\Microsoft\Excel\XLSTART\

In this path, "your name" is a placeholder for your actual Windows username. You should also note that the AppData folder is hidden, by default. This means that to access the folder, you'll need to enable the display of hidden files on your Windows system. You can do that by following this tip, over on the WindowsTips site:

https://windows.tips.net/T011964

With the template you just saved (step 8) still open, delete all the worksheets it contains, except for one. Use Save As to save this modified template into the same XLStart folder. This time, however, you should name it Sheet.xltx. (No macros here.) This is then used by Excel when you later add worksheets to an existing workbook.

The techniques described so far—setting up default workbook and worksheet templates—are great for creating brand new workbooks or worksheets that have the desired vertical alignment. If you want to affect an existing worksheet, however, it is easiest to use a macro. The following is a very simple example:

Sub SetVAlign()
    ActiveSheet.Cells.VerticalAlignment = xlCenter
End Sub

The macro sets the vertical alignment for every cell in the worksheet to center. (If you would prefer for it to be a top alignment, change xlCenter to xlTop.) If you prefer the macro to affect every worksheet in the current workbook, then it is only slightly longer:

Sub SetAllVAlign()
    Dim w As Worksheet

    For Each w In Worksheets
        w.Cells.VerticalAlignment = xlCenter
    Next w
End Sub

The macros could be added to your Quick Access Toolbar, to a ribbon button, or to a shortcut key so you can execute them with a quick click.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11900) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adding Half Spaces to Punctuation

Want a little more space just before some of your punctuation characters? You can add that spacing in a variety of ways, ...

Discover More

Clearing the Undo Stack in a Macro

Excel keeps track of the actions you take so that you can undo those actions if any are taken in error. You may want to ...

Discover More

Fields in Footnotes Won't Update Automatically

Fields can be very helpful for including dynamic information in your documents, such as cross-references. It can be ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Removing Borders

Need to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.

Discover More

Repeating Cell Contents

Want to repeat cell contents over and over again within a single cell? Excel provides two ways you can duplicate the content.

Discover More

Shrinking Cell Contents

Need to cram a bunch of text all on a single line in a cell? You can do it with one of the lesser-known settings in Excel.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 5?

2021-11-17 12:10:10

Tomek

If you want to have this setting available, but not necessarily start every new workbook with it, you can save the template to a location other than XLStart and with a meaningful name. You can save it to your Custom User Templates and have it available from File - New in the Personal group of templates (beside Office templates). You can also save it to your Desktop and to use it just double-click it there. Excel will create a new workbook based on the template you clicked. Just make sure that it is saved as template (xltx, .xltm, or .xlt).
Obviously, you can save it to any location on your hard drive or network drive, and just double-click it there. If the network drive is shared, others can use your template too.

<b>Beware</b>, opening the template from OneDrive for Business or SharePoint from within a browser does not quite work this way though.


2021-11-17 12:04:35

Tomek

If you want to have this setting available, but not necessarily start every new workbook with it, you can save the template to a location other than XLStart and with a meaningful name. You can save it to your Custom User Templates and have it available from File - New in the Personal group of templates (beside Office templates). You can also save it to your Desktop and to use it just double-click it there. Excel will create a new workbook based on the template you clicked. Just make sure that it is saved as template (xltx, .xltm, or .xlt).
Obviously, you can save it to any location on your hard drive or network drive, and just double-click it there. If the network drive is shared, others can use your template too.

<b>Beware</b>, opening the template from OneDrive for Business or SharePoint from within a browser does not quite work this way though.


2021-11-13 12:47:21

Tomek

Further to my comment of 2021-11-13 11:58:11

The paths I have given are for MS 365 Office, particularly the part "Office16". By navigating in the File Explorer to one of the system locations (%<name>%) I have given in my comment, you may be able to find your way to the XLStart folder. Alternatively, once in the system location, you may search for "XLStart"

Remember that the AppData folder and some others are hidden, by default. This means that to find and access a hidden folder, you'll need to enable the display of hidden files on your Windows system as described in Allen's tip.


2021-11-13 12:44:11

Tomek

It is important to know that if you create a new Excel file by selecting New - Blank Workbook in the File Tab, it creates a new document, which is not based on the Book or any other template. It is just a plain blank workbook with all Excel default settings. This applies also for selection of Blank workbook from the Start screen.

However if you click on the New button in the Quick Access Toolbar (visible while any tab except File is active), or if you press Ctrl+N (again, while any tab except File is active) the newly created file will be based on your Book template.

Also, if you disabled the "Show the Start screen when this application starts " in general options, the Excel will start with a blank file based on a Book template if any is present in one of the two XLStart locations.


2021-11-13 12:12:06

Tomek

It is important to know that if you create a new Excel file by selecting New - Blank Workbook in the File Tab, it creates a new document, which is not based on the Book or any other template. It is just a plain blank workbook with all Excel default settings. This applies also for selection of Blank workbook from the Start screen.

However if you click on the New button in the Quick Access Toolbar (visible while any tab except File is active), or if you press Ctrl+N (again, while any tab except File is active) the newly created file will be based on your Book template.

Also, if you disabled the "Show the Start screen when this application starts " in general options, the Excel will start with a blank file based on a Book template if any is present in one of the two XLStart locations.


2021-11-13 11:58:11

Tomek

There are actually two XLStart locations, at least in the recent versions of Excel. Default User Startup location as shown by Allen, and Default Excel Startup location. The latter can usually be found by typing "%ProgramFiles(x86)%\Microsoft Office\root\Office16\XLSTART" for 32-bit Office or
"%ProgramFiles%\Microsoft Office\root\Office16\XLSTART" for 64-bit Office into the address box in File Explorer. The name between the two % signs is a Windows system variable, which stores the default location of the program files.

This is important, because if there is a Book.xltm or Book.xltx file in the Default Excel Startup location, it will take preference before the one in the User Startup location. Same for the Sheet.xltx.

BTW, the Default User Startup location can be found by using "%appdata%\Microsoft\Excel\XLSTART" as the path in File Explorer.


2021-11-13 10:22:11

Irl

Please provide info on file paths for OS X as well.


2021-11-13 10:06:36

Ray McAllister

This tip is greatly appreciated. Buy even if vertical alignment is aet to top (my preference) for all cells, the row numbers in the left margin display at the bottom of rows that show multiple lines of text. Can the vertical alignment of those tow numbers be changed?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.