By Kate O’Neal
Many of us use Excel throughout our day and in varying degrees. I’ve spent enough time in it that I’ve found some timesaving tips for common tasks. In an effort to save everyone some time, I want to share them with you. They might not be useful to you now, but I recommend keeping them in your back pocket because I am confident one day they’ll come in handy.
- Need to merge a first name and a last name? Simple. Say the first name is in column A2 and the last name is in B2. You would use the formula =A2&” “&B2 to separate the two names by a space. If you want to merge them by last name, first name, simply enter this formula: =B2&”, “&A2
- Conversely, do you need to separate first and last names into two separate cells, or any data for that matter? Navigate up to the “Data” tab, and you’ll find the “Text to Columns” button. You can choose delimited (when your text is separated by characters like commas, tabs, etc.) or fixed width (when the columns are aligned with spaces between each field). I have always used the delimited option and can then separate the words by pretty much any character.
- Do you want to copy the format of a certain cell? Easy! Rather than manually having to format the size, font, and style, simply use the Format Painter button. First, you’ll want to highlight the cell with the format you want to copy. Click on “Format Painter,” then click and highlight any cells to which you want to copy the original format. Once you’re finished highlighting your cells, you will see the format of those cells change to match the first cell you wanted to paint. Note: This feature is also available in Word, Powerpoint, and I imagine other Office apps as well.
- Do you want to see if there are duplicates in a column? This is easily done by using conditional formatting. Start with the column you want to review for duplicates. Simply highlight the column, go to the “Conditional Formatting” button on the toolbar, click “Highlight Cell Rules,” then “Duplicate Values.” You can format cells that contain either duplicate values or unique values and choose the format that distinguishes them from the others.
- Do you want a date in a spreadsheet to automatically populate with the current date? Just use the =TODAY() formula in a cell so the current date will always appear.
- Lastly, are you format-crazy, like myself, and always want tables looking great that are also easy to sort? This is a big one I find myself using constantly. If you have multiple columns you find yourself sorting by, simply format it as a table rather than having to click around, add sort columns, and so on. Simply highlight the cells/columns you want to format to a table, navigate up to “Format as Table,” and click the style of the table you want. You’ll notice the highlighted cells have now turned into that table and you have sortable headers (see below where I sorted by ticket count from high to low). Note: I’ve found it difficult to remove the table formatting and revert back to the original format. Here’s how: once your data is formatted as a table, make sure your table is highlighted and then navigate to the “Design” tab. Simply click on “Convert to Range” and click “yes” to the prompt asking if you want to convert the table to a normal range. You’ll then see the table disappear (the formatting will still remain).
This is only a minuscule segment of some of the timesavers Excel can help with. I haven’t even scratched the surface with its abilities. If you have timesaving tips of your own, please share them with me!
Kate O’Neal
With multiple years’ experience working with clients in many industries, Kate brings her skills and positive attitude to Network 1’s team, clients and partners. She strives to provide the best service and solutions to her clients so they can achieve greatness through Worry-Free IT.
koneal@network1consulting.com or 404.997.7653
Network 1 Consulting is a 19-year-old, IT Support company in Atlanta, GA. We become – or augment – the IT department for law firms and medical practices. Our IT experts can fix computers, but what our clients value most are the industry-specific best practices we bring to their firms. This is especially important with technology, along with regulations and cyber threats, changing so rapidly. We take a proactive approach to helping our clients use technology to gain and keep their competitive advantage.
