Changing the Default Vertical Alignment

Written by Allen Wyatt (last updated November 13, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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 Microsoft 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

Inserting the Edit Time

One of the things that Word keeps track of is how long, in minutes, you've been editing your current document. This ...

Discover More

Changing Cell Patterns

You can shade your cells by filling them with a pattern. Here's how to select the pattern you want used.

Discover More

Converting a Range of URLs to Hyperlinks

Converting a single URL into a hyperlink is easy. Converting hundreds or thousands can be much harder if you have to rely ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Retaining Formatting After a Paste Multiply

You can use the Paste Special feature in Excel to multiply the values in a range of cells. If you don't want Excel to ...

Discover More

Ensuring Conditional Formatting and Data Validation is Copied

If you use an Excel worksheet for entering data (a quite common task, actually), then you need to be concerned with how ...

Discover More

Number Formatting Shortcuts

Keyboard shortcuts can save time and make developing a workbook much easier. Here's how to apply the most common of ...

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 2 + 8?

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.