I recently read a blog post about using Excel for SEO (Search Engine Optimisation). It mentioned a function to extract a domain from a URL. The function was from Google docs, not Excel. So I wrote an Excel formula to extract the domain from their list of URLs.
Selecting a column
To quickly select a column of data in a formatted table you have a couple of options.
Select a cell in the column and press Ctrl + Space Bar.
This will select the column of data. If you want the heading too, press it again.
You can also select multiple columns before using the shortcut.
This technique can take practice if your headings are in row 1.
If the heading starts in row 2 or below it is easier. See image below.
If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.
When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.
The image below is the correct arrow – this will select the column in the table only.
In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.
You may have noticed that Excel gives every chart a unique number when it creates the chart. It is displayed in the Name Box in the left corner above the grid. You have the ability to change that name and make it more descriptive.
When you are setting up a protected sheet that allows users to make inputs, a few option changes can make the user’s experience a lot easier.
We’ve all been there, our charts are looking just right and then some one inserts a column or changes the column width and throws out all our perfectly proportioned charts.
VBA If statement tip
When creating long VBA code it is common that the start of an If statement and the matching End If statement may not be visible on the same screen.
When scrolling around trying to understand your code it can be useful to include the If statement itself as a comment following on the same line as the End If command – see examples below.
If x=0 Then 'lots of code If y =1 Then 'lots of code End If 'If y =1 then End If 'If x=0 Then
The apostrophe is used to specify the start of a comment – you can have a comment following a line of code.
This structure can assist when trying to identify which End If statement relates to which If statement.
Entering Date and Time in Excel
There are shortcuts to enter
- the current Date Ctr + ;
- the current Time Ctrl + Shift + :
There isn’t one to enter both.
You can use them in sequence to achieve a date and a time entry.
In sequence press
Ctrl + ;
Ctrl + Shift + :
The space separates the date and time and Excel will recognise the entry as a date and time.
If you change a formula in someone else’s file you should tell them. In fact there are a few things you should do to make it easier. This can apply to changes to your own files as well.
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.
Filtering blanks made easy
In Excel the “Blanks” option is usually at the bottom of the list. This slows down selecting it.
If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it – see image below.
But the word “Blanks” is searchable, so if you type b in the Search box – your work is done – no scrolling required – see image below.
If your column contains text you might need to type in bla.
There are certain situations when you can’t “unhide” columns on the left and/or rows at the top and it is not a sheet protection issue.
There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.
Columns in a Text Box
You learn something new every day.
Today I discovered that you can have columns within a text box.
If you right click the text box and choose Format Shape the Task Pane below should open on the right.
Click the third icon (Size and Properties) at the top and then open up the Text Box options.
The Columns button allows you to specify how many columns plus the gap between them.
When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick.
Copy Across Shortcut
In the structure below let’s assume you want to copy the SUM formula from cell B5 to cell C5.
Obviously you could use copy and paste, but that would require a few keyboard presses or mouse clicks.
Given that we already have cell C5 selected we can use
Ctrl + Shift + >
This copies whatever is in the cell on the left to the current cell.
Excel has a shortcut that allows you to follow a link to its source but it has a limitation find out how to get around that limitation.
When data is imported into Excel sometimes the values come in as text rather than values. Most functions can’t perform any calculations with text numbers, but one can. See how easy it is to add up text values.
Word tip – increase font size
I recently found this keyboard shortcut for MS Word to increase the font size of the selected word(s).
To reduce the font size use
Formatted Tables are great but there is an issue when it comes to copying formula that use the table names (Structured References). There are two techniques that cope with this limitation.
A drop down list in Excel can help speed up data input and ensure the user has entered a valid entry. If you have only a few choices, creating the drop down can be a quick process.