Welcome to your weekly Excel tips update - just a little morsel of new Excel skills to learn before the weekend. This week we cover how to combine text from multiple cells, a refresher on some of the most commonly used keyboard shortcuts, and a function that will turn a table of web addresses into clickable links. Let’s get started!
A quick Excel tip for working with text
In last week’s tips, we covered the CONCATENATE function, which can be used to join multiple cells together. There’s another way to join text in Excel using the ‘&’ symbol, which allows you to create text based upon other cells.
Say in A1 you have the name of a member of staff, their start date in B1, and their department in C1. You want to create a cell which says ‘John Smith joined the Marketing department on 01-Jan-2019. You could enter:
=A1&” joined the “&C1&” department on “&TEXT(DATE(B1,”dd-mmm-yyyy”))
A quick Excel shortcut you should already be using!
When training someone new on Excel, it’s easy to assume that common keyboard shortcuts like Ctrl + S to save, or Ctrl + C to copy are already known, as they apply to almost any computer software, but without them, it’s hard to become a fast, productive user of any program.
One shortcut people often miss is Ctrl + X to cut (i.e. copy and move), and Ctrl + Z to undo. These aren’t just Excel shortcuts, they apply to most software across Windows and Mac.
A great function for creating links in Excel
There are several ways to create hyperlinks (links to a file or a website) in Excel. If you type a valid website address into a cell, Excel should automatically recognize it as a link, and make it clickable.
However, if you’ve received a spreadsheet of links in a list, doing this could be time-consuming to go into each cell and edit the text to turn it into a link. You can get around this by using the HYPERLINK function.
Simply type =HYPERLINK( followed by the cell location of the link, and the cell location of the text you’d like the link to display. For example, if you have ‘Google’ in cell A2 and www.google.com in B2, you could type =HYPERLINK(B2,A2) to create Google.
Next week we’ll be looking at freezing rows or columns in Excel to keep them visible while scrolling, how to enter today’s date using a shortcut and learn to rotate data from rows to columns or vice versa using TRANSPOSE.