Written by Allen Wyatt (last updated November 13, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
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:
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.
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!
Want to change the size of the font within a worksheet? Excel allows you to choose from a list of sizes, as well as ...
Discover MoreWant to repeat cell contents over and over again within a single cell? Excel provides two ways you can duplicate the content.
Discover MoreNeed 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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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?
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments