As an analyst (or manager), I bet a good portion of your Excel time is spent writing formulas and getting the results.
So today, let us learn 5 important keyboard shortcuts that will save you a lot of time and help you write better formulas.
- F2: Edit a formula cell. When you select a cell and press F2, Excel places cursor at the end and lets you edit the cell value / formula.
- F3: Paste names in to formula. When you have a lot of names, often remembering them can be tricky. Whenever you want to type a name, press F3 instead. Excel will show a list of all names and pick what you want.
- Tab: Auto-complete functions, names, structural references. As you start typing a formula, Excel shows auto-complete suggestions. Use arrow keys to select the function, name or structural reference you want, Press Tab key to let Excel type it for you. You can save a ton of time by just arrow key + tabbing.
- F4: Switch reference styles – Absolute > Mixed > Relative > Absolute. When typing formulas, often you may want to change a certain cell reference to Absolute or Mixed or Relative. You can use F4 key to do the switching. Just place cursor inside the range / cell address and press F4 to cycle thru all available reference styles. (more: Guide to Excel cell references and when to use what?)
- ALT + ESF: Paste Formulas only. If you just want to make a copy of the formulas and omit cell formatting etc., copy the cells with formulas, go elsewhere, press ALT + E and then S and F. And you get a copy of the formulas alone.
Bonus Shortcuts & Tricks:
Writing and editing formulas is such an important part of Excel that there are many other useful shortcuts and tricks. Here are a few of my favorites:
- F9: Evaluate selected portion of a formula. Select a portion of a formula and press F9 to evaluate it alone. See the results and debug easily. (related: debug formulas using F9)
- CTRL + ` : Show formulas. Very useful when explaining your worksheet to others. Press CTRL + ` (back quote, usually the key above tab on left) to on / off show formulas mode. (related: auditing formulas & spreadsheets)
- Use mouse to edit formula ranges: When you select a cell with formula and edit it (by pressing F2), you can see these blue, green, red rectangles around the cells to which the formula is talking. You can move these rectangles or resize them to edit the formula input ranges. Very useful and very time saving. (more: Using mouse to save time in Excel)
- Fill down / side ways: Once you have a formula in a cell, you can drag it down or sideways (using mouse) to fill the formula down or across.
What are your favorite shortcuts for writing / editing formulas?
I am sure there are tons more shortcuts that I have omitted. So go ahead share your favorite ones in comments. Teach us something new. Go.
Also check out: Comprehensive list of Excel keyboard shortcuts.
14 Responses to “How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]”
in C3=2016
in C4=3
in C5=1 (the first next year with three Friday the 13ths)
=SMALL(IF(MMULT(--(MOD(DATE(C3+ROW(1:1000),COLUMN(A:L),13),7)=6),ROW(1:12)^0)=C4,C3+ROW(1:1000)),C5)
formula check in the next 1000 years
This will generate a table of counts of Friday the 13th's by year. If I didn't screw it up the next year with three is 2026.
I created a simple parameter table with a start date and end date that I wanted to evaluate. That calculates the number of days and generates a list of those days. Then filter and group. The generation of the list in power query (i.e. without populating a date table in excel) is pretty cool, otherwise this isn't really doing anything than creating a big date and filtering/counting.
let
Source = List.Dates(StartDateAsDate, Days2, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
With the parameters replaced by values should you want to play along at home. This runs for 20 years starting on 1/1/2016.
let
Source = List.Dates(#date(2016,1,1), 7300, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
=MATCH(3,MMULT(N(WEEKDAY(DATE(C3+ROW(1:100)-1,COLUMN(A:L),13))=6),1^ROW(1:12)),)+C3-1
It should be pointed out that Alex's solution, unlike some others, has the additional advantage of being non-array. My solution was nearly identical but with -- and SIGN instead of N and 1^.
=C3-1+MATCH(3,MMULT(--(WEEKDAY(DATE(C3-1+ROW(1:25),COLUMN(A:L),13))=6),SIGN(ROW(1:12))),0)
Sub Friday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim r As Long
Range("C7:C12").ClearContents
StartDate = CDate("01/01/" & Range("C3"))
EndDate = CDate("31/12/" & Range("C3"))
r = 7
For x = StartDate To EndDate
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Cells(r, 3) = Month(x)
r = r + 1
End If
Next
End Sub
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
*RE-POST as not equal did not show earliuer
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear NE Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
earlier*
I've a doubt with using array formula here.
In sample workbook, I tried to replicate the formula again.
=IFERROR(SMALL(IF(WEEKDAY(DATE($C$3,ROW($A$1:$A$12),13))=6,ROW($A$1:$A$12)),$B7),"")
For this I selected C7 to C12, and typed the same formula and pressed ctrl+alt+Enter. But in all cells it is taking $B7 (and not $B7, $B8, $B9.... etc)
and since it is array formula I can't edit individual cell.
Please guide.
Thanks
Hi Chandoo,
Cool stuff. You need to clarify that the answer of 5 represents the 1st month in the year that has a Friday the 13th, and not the number of Fridays the 13th in the year. Subtle, but important difference.
Thanks,
Pablo
I like the MMULT() function far more, but here's how I would have tackled it. It uses an EDATE() base and MODE() over 100 years. I'm assuming that 100 years is enough time to catch the next year with 3 friday 13th's. Array entered, of course.
{=MODE(IFERROR(YEAR(IF((WEEKDAY(EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))))=6), EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))), "")), ""))}
Finding all the Friday the 13ths in a Year:
=SUMPRODUCT((DAY(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))))=13)*(TEXT(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))),"ddd")="Fri"))
{=sum(if(day.of.week(DATe($YEAR;{1;2;3;4;5;6;7;8;9;10;11;12};13);1)=6;1;0))}
just list the years