Since Excel doesn’t have the same set of robust text transformation tools as Word, we need creative ways to get the same effects sometimes. When it comes to auto-capitalizing whole words or first letters of words, there are two functions you can use:
- =UPPER() will capitalize the entire cell contents
- =PROPER() will capitalize first letters of each word in a cell
In the GIFs below, you’ll notice I demonstrate both. The steps for both are exactly the same, so just pick the formula/function you need.
Since nobody wants to add another column to a sheet unnecessarily, we’ll just temporarily create a column for the calculation, copy the result, then delete it. You may, alternatively, wish to just hide it for later use or keep it alongside the original for a purpose.
Insert column and formula
- Insert column
- Place formula of choice in top cell of column and drag down (lower right-hand box/handle of cell) or press enter if in table format as seen below
Copy new values over originals and delete temporary column
Next we’ll use the “Paste values” feature to remove the formula dependency of the resulting data and keep just the newly formatted text.
- Copy values
- Paste (special) values only over original column values to replace them with the correct capitalization version
- Delete temporary column
Did you know that if you use an Excel Table, as your screenshot suggests, you can use the column name in the formula? This is called “structured referencing” and would look like =UPPER([@Animal]). You can simply click into the column you want to reference when you are writing the formula. In fact, table formulas work better if you don’t use A1 references, because these can break when rows are inserted or deleted.
Great tip! Thank you for adding