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!
This post is originally from 2016. If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca
Flash Fill was introduced in 2013, but I have clients who are just upgrading to 2013 this year. This is the kind of feature that makes upgrading worthwhile.
Flash Fill is a new tool introduced in Excel 2013. Its a simple tool to handle a frequent problem. You have a small set of data and you need to break it apart into separate columns or join separate columns of data together.
Previously, I would have handled it with a series of text functions in Excel (LEFT, RIGHT, MID) combined with FIND and LEN if the data was complex enough. But if the data set is small, writing a formula sometimes seems like an overly complicated answer to a simple problem (why not just retype?).
Now Flash Fill is stepping in to help you handle this problem. If you give it a series of data (column orientation only) and an example of the pattern you want to extract, it will extract the data for you.
You can see once the second name is typed in the column adjacent to the list of full names, Flash Fill is able to see the pattern and offer all the first names in the list. Pressing enter autocompletes the action and the names are filled in. To do this, there can not be more than two blank columns between the source data and the resulting column. You can use the Ctrl + e shortcut to start flash fill.
You can click the Flash Fill icon to display the menu, accepting the suggestions will have all the names autocomplete.
Here is an even trickier scenario, in the list above some names have two middle initials. Using the “default” flash fill means only the second initial will display in those names. However if I return to any of the names on the list (with two initials) and correct the example to two initials, all of the two initials examples will be extracted.
I find that seriously impressive.
I can split data in a cell into multiple columns and I can also use Flash Fill to join multiple columns of data together.
The same technique used above. Note that I’ve been able to add commas and periods to the text as well.
In the same way, I can use Flash Fill to apply formatting, in this case putting a space between the first and second part of a postal code. You can also use it to format telephone numbers and date information.
I was (and still am) excited to share Flash Fill with my favourite clients. If you are interested in becoming one of my clients, drop me a line at catharine@mytechgenie.ca
Here’s a quick keyboard tip, instead of copying and pasting the same information in multiple cells (too many steps!), try the following.
Pre selecting the cells you want to enter data in.
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!
In a previous post I showed how I entered a column of repeating dates when building my Social Media spreadsheet. The next thing I like to do, is colour code those dates so that I can see at a glance when the weekend dates are. For this I use the WEEKDAY function in Excel.
Point the WEEKDAY function at a date and it will return a number from 1 thru 7 indicating what day of the week the date is. In this case the formula reads =weekday(A2,2)
The 2 in the above formula is the return type, and here indicates that the week starts on Monday. This means that Saturday and Sunday will return values of 6 & 7.
This is perfect for using with conditional formatting.
If I plug the following formula into the conditional formatting dialog box
=(WEEKDAY(A2,2))>5
I am testing for values above 5, namely the weekend. So I can use this to put a colour fill in those dates so that they stand out.
Obviously, the Results column isn’t needed because the formula is actually residing in the Edit Formatting Rule dialog box.
This is the second post discussing using Conditional formatting with a Social Media spreadsheet. Check out this previous post for another example of using conditional formatting.
This post is originally from 2016. If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca
When I’m setting up my Social Media spreadsheet in Excel, I like to limit the number of scheduled Facebook entries per day. Over time, I’ve come to think that 4 Facebook entries per day is a reasonable maximum. This lets the librarian post “live” when things are happening in the library without clogging up our follower’s feeds.
So I want to create a column of dates that looks like this:
The quickest way to do this with minimal typing is to use the Fill Series dialog box. Since Excel 2007, you can find it under the Fillmenu on the Hometab.
To use the Fill Seriesdialog, select the range of cells you want your dates to be entered in. Make sure the first cell in the range has the starting date. Then select the Fillbutton and choose Series.
Enter a Step value. In this case, because I want 4 repeats of each date I’m using .25 as the Step value. If I wanted 5 repeats, I’d use .20 (and so on).
If you don’t feel like calculating how many cells to select when doing this for a date range that spans a couple of months; try using a Stop value . With a Stop Value, the series will stop at the first instance of the date entered into the field. Otherwise, the series will fill the entire selected range. ( In the picture above the full date is not displayed in the field, it was actually 06/01/2016.) Using a <em><strong>Stop Value</strong> </em>allows you to make a rough selection (say 500 cells) and Excel will stop when the series runs its’ course.
This post is originally from 2016, however Filling a series is still as useful in 2020 as it was then.
If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca
Sometimes, when you’re teaching, its not about the complexity of the subject. Sometimes its a very simple piece of information that students get the most “mileage” out of.
When I’m teaching students MS Excel, the simplest thing that I teach them about is Named Ranges. Its the simplest thing to talk about, but the uses for ranges go on and on.
Above you see a standard Excel spreadsheet. Adding a range name or two (or ten) can help make it much easier to work with.
A range name can refer to a single cell or a group of cells, here I’ve selected the cell containing the total for the six month period (H11).
Click into the Cell address box (circled in red) and type in the desired name. There are some simple rules about naming ranges; the name can’t start with a number, can’t look like a cell reference (imagine how confusing that would be) and can’t use spaces and special characters (notice I’ve used an underscore to separate words). But after that it is up to you, to make your range name meaningful.
If you are going to add a range name to a group of cells, select them and type the name into the cell address box. The most frequent mistake students make at this point, is that they forget to press the Enterkey to confirm the range name.
Now, how do you use these range names?
First, you can quickly jump to your named ranges by using the drop-down menu. When you click on the drop-down menu in the cell address box, you’ll see a list of all the ranges you’ve added to your spreadsheet. Regardless of what sheet they are on. So you can use this to quickly jump to those cells that you work with again and again.
Second, you can replace cell references in a formula with range names. Does =SUM(January) seem easier to read and understand than =SUM(B2:B10)? Then a formula that uses range names will make your spreadsheets easier to read.
Third, you can use range names in conjunction with all sorts of other Excel tools. As an example, try using range names with the Data Validation tool.
In the sample above, a range name provides the source list for a drop-down list.
Resulting in this drop-down list. The list will update as the list of animals changes on Sheet1.
This is a more elegant solution for using drop-down lists, since it means your source lists can be kept on another sheet, and not clutter up the working area. This is something that is impossible to do, without using a range name.
So faster navigation, easy to read formulas and access to more powerful features in Excel. What’s not to love about range names?
Looking to make a powerful spreadsheet or do you want me to make it for you? Drop me a line at catharine@mytechgenie.ca