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
Click to enlarge
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.
“Paste Values” icon when pasting in Excel. This option copies just the values resulting from a formula so you don’t “break” your data/calculations when moving/deleting.
Copy values
Paste (special) values only over original column values to replace them with the correct capitalization version
This morning I pasted a list of values into Excel in which the pattern consisted of a “good value” followed by that same value with a minor adjustment. The second, near-duplicate value wasn’t useful to me so I wanted to filter out all even rows so I’d just have the first of each near-duplicate.
So to filter odds and evens, we’ll add a new column to the right of our data and set the formula to:
=ISEVEN(ROW())
This evaluates the data’s row number as even or odd and will return TRUE if the number is even or FALSE if not. Double-click the handle in the lower right of the cell to fill down.
Then we filter the column for just TRUE (evens) or FALSE (odds). You can right-click the value you want to keep > Filter > Filter by Selected Cell’s Value
A simple trick in Excel allows you to remove duplicate values from a column in Excel. Depending on your version of Excel, yours may look a bit differently but the process is nearly the same. Below, I’m using Excel 2016.
1. Select the column, or the values from which you’re removing duplicates. Note: You could also just start with step 2, and then select the relevant column(s) from the resulting dialog. 2. Go to Data > Remove duplicates (in the Data Tools panel)
3. Confirm if your column has a header (column title or not) and click OK.
4. Excel will confirm the number of duplicates found and how many remain. Click OK.
But adjusting the same widths in datasheet mode (quick edit, for example) is a bit different. After a bit of fiddling around, I found an answer that will allow you to adjust column widths for both standard and datasheet view types using just CSS.
The difference is in how you reference the column names in the css:
Standard (catches both filterable and non-filterable columns such as multi-line text): th.ms-vh2-nofilter div[DisplayName=’Column1′],th.ms-vh2 div[DisplayName=’Column1′]{…
Datasheet: th[Title=’Column1′] {…
Everything that follows that first line is the same in both types of views, fixing a minimum and regular width property for the column(s).
I’m honored to be among many fantastic presenters for SharePoint Fest Seattle. When first learning SharePoint, SharePoint Fest Seattle was the first big conference I attended. I was an administrative associate at the time. I remember attending a workshop on SharePoint Designer workflows by Vlad Catrinescu and thinking it was the coolest thing I’d ever seen.
Each night of the conference, I’d stay up late working on things I’d learned or ideas I’d gotten during the day. After the conference, I dove all in and ended up working full-time in SharePoint. My job transformed into a SharePoint Business Analyst role. Fast forward, now I’m a Microsoft MVP, Microsoft 365 Certified Enterprise Administrator Expert, author, blogger, user group leader, and a SharePoint Systems Engineer at LMH Health.
I’m continually wowed and re-energized by all that’s coming out of Microsoft. And I’m excited to be on the other side of the podium for a few sessions this year, sharing some of my favorite topics. And, as always, I’m looking forward to sitting in the audience as well for what is certainly one of the year’s best professional development opportunities.
Come discover how SharePoint Fest Seattle can expedite your career growth through professional development and inspiration. Register today at https://www.sharepointfest.com/Seattle/ and use code Chamberlain100 to save $100 off your pass.
I’ll be presenting these three sessions, as well as sitting at the “Ask the Experts” booth a couple times. I hope I’ll see you there!
INT102 – Building the Intranet of the Future: Using SharePoint to Empower Collaboration
Automation, organization, incredible search capabilities and user interactivity; all things your organization can have in choosing to build an intranet with SharePoint.
We’ll discuss avoiding File Dump 2.0 by exploring best practices in internal communications and collaboration via intranets, rethinking forms and minimizing busy work by utilizing automation capabilities in SPD workflows and Microsoft Flow and designing intuitive navigation.
CM202 – SharePoint wizardry for content management, archiving & retention
Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”?
Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!
BV303 – You’re the one, OneNote!
Are you looking for more productivity in your life? Sticky notes leaving you…unsatisfied?
Sit back and enjoy this journey through the sweetest productivity story ever told, where you’ll be inspired to take “note” of new ways of working more efficiently day-to-day with OneNote. When it comes to working smarter and not harder, OneNote is the one.
