Category Archives: Excel

Highlighting the Current Year

Here is a version of a spreadsheet that I’ve been using for a couple of years to track and plan capital purchases. A number of people review this spreadsheet and I want to make it as easy as possible for them to read the spreadsheet. You’ll notice that the Budget year is highlighted in green and items being purchased in that year are highlighted as well. This is accomplished with our friend conditional formatting and the following spreadsheet functions:

  • ADDRESS
  • ROW
  • COLUMN
  • MATCH
  • INDIRECT
  • ISBLANK
Spreadsheet with automatic highlighting
Capital Budget Spreadsheet – note the row and column (year) highlights

This spreadsheet makes use of a helper column of formulas. Rows where the value equals TRUE are highlighted.

The Helper column holds formulas
The Helper column holds formulas

Cells give a value of TRUE when there is a value for that row in the Budget Year selected in cell A1. You can see that 2017 has been selected as the Budget Year and that rows 9 and 20 have a value for that year and are highlighted as a result.

This is the formula that returns the value

=ISBLANK(INDIRECT(ADDRESS(ROW(),MATCH($A$1,$F$2:$AU$2,0)+5,3,TRUE)))=FALSE

Working from the interior of the formula outward.

MATCH($A$1,$F$2:$AU$2,0)

This looks for a match between the value in A1 and the Budget year headings which start in cell F2 and go to AU2 (the year 2033, which is incredibly optimistic – but that is another story). MATCH returns the number of the first item in that array of cells that matches the value in A1. This is why even though there are two columns for every year (a Budget column and an Actual column) MATCH will only return the Budget column, as it is the first value to match.

So the result of MATCH($A$1,$F$2:$AU$2,0) is 9

However, if I actually want to capture the column I need to to add 5 to compensate for the fact I have 5 columns (A-E) before column F and the year headings begin.  This is why I’m adding 5 in the formula.

MATCH($A$1,$F$2:$AU$2,0)+5 =14

In the next step I use ADDRESS and ROW to capture the address of the cell I’m testing.

ADDRESS(ROW(),14,3,TRUE))

ROW() captures the value of the row of the cell where the formula is written. If the formula is in A3, then row() returns 3.

ADDRESS turns the cell address of the referenced cell (not its’ contents). In our example; ADDRESS(3,14,3,TRUE)=”$N3″

The ISBLANK function in the next step has a bit of a hiccup with that “$N3” string, so we use INDIRECT to convert that string to something ISBLANK can understand.

Finally, ISBLANK is used to test if there is a value in the referenced cell or not. If there is nothing in the cell ISBLANK = TRUE.

If ISBLANK = TRUE, then the last portion of the formula looks like this: TRUE does not equal FALSE, so the result of the formula in cell A3 is FALSE.

I could have put that formula into the conditional formatting dialogue – but for clarity and ease of working I choose to make the helper column instead.

In the conditional formatting dialogue I’ve used the following formula =$A3=TRUE

I’m using a simpler version of the formula in the conditional formatting dialogue to highlight the year.

=MATCH($A1,$F$2:$AU$2,0)+5=COLUMN()

In this case I find the column number of the year and test to see if it matches the column number of the current cell. If it does then the cell receives a green highlight fill.

Cell A1 uses Data Validation to offer the user a nice drop-down list of years.

Excel: Frozen

Freezing panes is a basic tool to make a large spreadsheet easier to work with. In my social media spreadsheet I like to freeze the header row into position. Then no matter how far I scroll down the sheet, the columns are labelled.

To turn on frozen panes, select the cell below the row & column you wish to freeze into position. Since I don’t want to freeze any columns, I select cell A2.

Freezing Panes using the Active Cell position
Freezing Panes using the Active Cell position

Select the View Ribbon, Click on the Freeze Panes button, and choose Freeze Panes (or Freeze Top Row in this scenario).

Freezing Panes Results
Freezing Panes Results

Now you can scroll for hundreds of rows, and each column is nicely labelled – no guessing!

Excel: Select Visible (2)

The Select Visible Cells Only button on the Quick Access Toolbar
The Select Visible Cells Only button on the Quick Access Toolbar

The Select Visible Cells Only function is so useful, I like to add it to the Quick Access Toolbar (QAT) in Excel. These instructions are based on Excel 2010, but will be similar in all current versions of Excel.

Quick Access Toolbar Customization
Quick Access Toolbar Customization

The Quick Access Toolbar starts in the top right corner of the Excel window.  The customize button is circled in red. Clicking on that button displays the menu shown below.

Move the QAT under the ribbon
Move the QAT under the ribbon

The first change I like to make is to its’ position. I like to move it under the Ribbon, since there will be more room for buttons there. Over time I tend to fill the QAT up with frequently used tools.

After I move the QAT below the ribbon, I go hunting for useful commands to add.  Click the More Commands… option and the Customize the Quick Access Toolbar dialog opens up.

Customize the Quick Access Toolbar - Popular Commands
Customize the Quick Access Toolbar – Popular Commands

The dialog box defaults to Popular Commands.  Try scrolling through this list and find the Format Painter. Press the Add button, to add it to the Quick Access Toolbar. This is a useful tool to have at hand!

By clicking on the Choose commands from drop-down list, a selection will be displayed.

Drop-down list of source commands.
Drop-down list of source commands.

Select All Commands from this list. Hundreds of Excel commands will display, and this is where it is useful to know the name of the command you are looking for. Scroll until you find Select Visible Cells.

Finding the Select Visible Cells command
Finding the Select Visible Cells command

Select it, Click on the Add button, and click OK.

Simply select the cells you wish to copy and press the Select Visible Cells button. Paste your information and only what you see will be pasted.

Excel: Select Visible (1)

I built my Social Media spreadsheet in an Excel spreadsheet with all the tools I want built in (formulas, conditional formatting and data validation). Ultimately, I will transfer my information into a stripped down spreadsheet in csv (comma separated) format. This is the format that Google Calendars will accept.

When I transfer my posts to this spreadsheet, I don’t want to include any blank rows AND I only want to copy and paste once. How do I perform this little piece of magic? I use the Excel command for selecting visible cells only.

Go To Special Dialog, select Visible cells only
Go To Special Dialog, select Visible cells only

Tucked away in the Go To Special dialog is the option for selecting only the visible cells in a region. This takes what could be multiple copy/paste operations and condenses them into one step.

First filter your data so that blanks do not appear, then press the F5 function key to bring up the Go To dialog box.

GoTo Dialog - Special Button
GoTo Dialog – Special Button

Press the Special button to open the Go To Special dialog box, choose Visible cells only and press OK. Now when you copy the selected cells, only the cells you can see are copied.

In my next post I’ll show the method to put this useful button on your Quick Access Toolbar.

 

Excel: Filtering in action

SortandFilter
Finding the Filtering button

Since Excel 2007, the Filter tool has been on the Home ribbon, under the Sort and Filter drop-down. The Filter tool can be applied to any spreadsheet where every row is a new record. Excels’ guesses about what and how to filter will be more accurate if the data has a header row. Your (human) life will be easier if you give that row a little formatting to make it stand out from the data.

If your data has gaps, select all the data (including the header row) and apply the filter. Once the filter has been applied, little triangles will appear beside each header label.

Filtering Drop-Down panel
Filtering Drop-Down panel

Now you can use each header to filter the data.  Click on the filter drop-down and the panel will open as you can see in the picture above. Clear the check boxes beside the entries you don’t want to see. Then click the OK button. You can spot filtered data, because the row headers will be bright blue (and row numbers will be missing as data is filtered out). The columns where filtering is applied will have a filter icon (circled in red in the picture).

Filtering Applied
Filtering Applied

Once the filters are in place, I can filter out blanks or filter blanks in to find openings in our social media schedule. I can quickly look for Posts and Tweets with images, to ensure the image information is present. I can filter down to a single subject. All of these filters make managing my posting schedule MUCH easier.

Excel: Validate!

Data Validation dropdown
Data Validation drop-down

When building my Social Media spreadsheet, I want to enter my subject keywords and trigger keywords consistently. Minor typos can make it difficult to find all the relevant posts and worse; prevent scheduled posts, tweets and pins from being published on time. This is why I find the Data Validation feature in Excel so useful. As you can see in the picture above, once Data Validation is in action, my data entry is restricted to a preset list of options.

DataValidationRibbon
Find the Data Validation tool on the Data ribbon

Since Excel 2007, the Data Validation tool has been on the Data Ribbon. Simply select the cells you want to apply Data Validation to and press the Data Validation button and select Data Validation. Then the Data Validation Settings dialogue box will appear.

Data Validation Settings
Data Validation Settings

To keep the active sheet “clean”, I use a named range on another sheet as my data source (I’ve talked about that previously). Here you can see it’s called PostTypes. But you can enter short lists directly into the Source box:

DataValidationSettings2
The list entered into the source box, each item separated by a comma.

However, I find in the long run (especially for long lists) keeping the list source on another sheet makes maintenance easier.

Excel: Concatenation for the Social Nation

Concatenation results
Concatenation results

As I’ve worked more with scheduling posts, tweets and pins, I’m trying to make the most of the Subject line used by Google Calendar.

Google Calendar Subject line
Google Calendar Subject line

I’ve found that if I combine a meaningful keyword describing the post(or tweet, or pin) plus the phrase that triggers the IFTTT action, then managing the scheduled posts once they are uploaded into Google Calendar is a bit easier. It also makes it easier when I’m filtering and managing the spreadsheet too.

In my spreadsheet I use a separate column each for subject keyword and for subject trigger phrases (actually I’m paring those down to keywords too). But I want them joined together to create the actual subjects. To do this, I use the Excel CONCATENATION function. Which is most simply represented by the & symbol. In the example at the beginning of the post you can see the formula:

=B103& ” ” &C103

In this case I’m using the & symbol to join the values of cells B103 and C103 together with the string ” ” in the middle to create a nice space between words. This allows the subject phrase to be created automatically once I’ve selected the subject and trigger keywords.

Excel: Multiple Cell entry

Here’s a quick keyboard tip, instead of copying and pasting the same information in multiple cells (too many steps!), try the following.

  1. Pre selecting the cells you want to enter data in.
  2. Enter the data – BUT instead use the Ctrl + Enter keyboard shortcut to confirm the data. This will duplicate your data entry to every cell you have selected.

The toughest part of this shortcut is remembering to use it!

Excel: Adding time

Addingtime1
Automatically adding 30 minutes to the Start Time

In my Social Media spreadsheet I want to add 30 minutes to the starting time for the post.  Why 30 minutes? That’s the default scheduling time in Google Calendar.

To do this, I use the TIME function. The TIME function has 3 arguments; hour, minute and seconds – all three arguments are required. So my formula would look something like this:

=F2+TIME(0,30,0)

So, I’m adding 30 minutes to the value from cell F2.  Easy!