We are launching a new version of F1F9’s keyboard shortcuts. You will find them available to download in an Excel workbook just below.
The shortcuts are stored as macros in the workbook. On opening the workbook, you may need to click on “Enable Content” or “Enable Macros” to make sure things run properly.
Once done, the keyboard shortcuts will be active if the workbook is open.
Tip: save the F1F9 keyboard shortcuts workbook to a folder on your desktop called “F1F9 Macros”
Using the keyboard shortcuts
F1F9 has designed additional shortcuts to increase a modeller’s productivity. You do not have to use them to build a financial model, but many of our training clients tell us that they find them extremely useful. For example: Ctrl + Shift + A will copy the contents of a selected cell across all remaining visible columns in the row of that selected cell. That’s useful if you are building a model with consistent formulas.
You can test the shortcuts by having two workbooks open at the same time: F1F9’s keyboard shortcuts and a blank workbook.
Switch between the two workbooks using Ctrl + Tab (that’s a standard Excel shortcut).
In the blank workbook, type a number in a cell of your choosing, tap “Enter” and then copy across the selected cell using Ctrl + Shift + A.
You will find a list of all of F1F9’s keyboard shortcuts here. Alternatively, you will find them documented in the workbook itself.
Hiding the keyboard shortcuts
Now you have got the shortcuts working, you may want to hide the F1F9 keyboard shortcuts workbook so that it sits invisible as you work and does not get in the way. Make sure you are looking at the F1F9 keyboard shortcuts workbook and use the standard Excel shortcut Alt W, H to hide it.
If you wish to unhide it again, then try Alt W, U.
Tip: a hidden workbook is still open – it is just you can’t see it. Hiding a workbook is quite different from closing a workbook down.
Opening the F1F9 keyboard shortcuts automatically
You can change your settings in Excel so that the F1F9 keyboard shortcuts workbook opens when you open Excel.
Firstly, save a copy of the F1F9 keyboard shortcuts workbook in a dedicated folder. For example, set up a folder on your desktop called “F1F9 Macros”.
Next, take a copy to the clipboard of the folder’s file path. You will find that by clicking in the formula bar relating to the folder in File Explorer.
In this instance, the file path is “C:\Users\Andrew\Desktop\F1F9 macros”.
Now move back to Excel and open up Options using Alt F, T. Select the “Advanced” tab and find the menu tab called “At startup, open all files in:”. Paste the contents of the clipboard into the menu box.
Tap “OK” and then come out of Excel all together.
Next time you open Excel, the settings in Excel will prompt it to open up all files that you have stored in your chosen file path. This means that F1F9’s keyboard shortcuts will be available to you when you open up any spreadsheet that you wish to work on.
And if you have saved the F1F9 keyboard shortcuts file in a hidden state then it won’t get in your way.
Tip: only store in your chosen folder those files that you wish to have open every time you open Excel.
Comments
Will I will be able to train well the financial modelling course with Mac book coz I have apple laptop or is it designed well for windows laptop
Hi Tulika,
Apologies for our late reply. You’ll be able to do the course on a Mac – a couple of shortcuts are different, but we give some guidance on that in the free 31 day course.
Thank you for the improved version of the shortcuts. Three questions/comments:
1. The anchoring Ctrl Alt A could be very useful, but I don’t see it working on my laptop and from the VBA code I neither see how it could work.
2. Is there some explanation on how to use the Ctrl Alt R function?
3. To save the workbook in hidden state, click ‘hide’, close excel and accept saving changes.
Hi Giel,
Please find below our suggestions / comments:
1. Ctrl + Alt + A shortcut is used to anchor links. This is how it works;
a) Select the cells with links in which you want to add / remove the anchoring.
b) Press Ctrl + Alt + A and it will show a dialogue box.
c) Tick the type of anchoring you want to apply in the selected cells and press ‘Ok’.
*Note:* Anchoring macro only works in cells with direct links (i.e. no formula (i.e. with +/- etc.) or excel function should be present in the selected cells).
Please let us know in case you are still facing an issue with this macro in your excel even after following the above mentioned steps.
2. Ctrl + Alt + R macro is used to relocate inputs from the calculation sheets to the Input sheet.
a) The shortcut for relocating input have some conditions which have to be fulfilled before using it:
– The cell which is containing input should be marked with input shade (Ctrl + Shift + I)
– Input should be in column F (constant inputs) or column J (series inputs).
b) When we use shortcut Ctrl + Alt + R, it will show a dialogue box which will ask for the sheet name. After adding the sheet name it will directly relocate the input to the input sheet or preferred sheet. Please note that the input will be added at the end of the sheet.
Please find more information for relocating inputs to input sheet in *FFSM – Fast Financial Statement Modelling Course”, in Model Finalization section, Module – 4, Rough work.
(Direct link: http://courses.f1f9academy.com/course/383243/module/1631811?LPId=17736)
The usage of macro Ctrl + Alt + R is efficient as it will save us the time of moving the inputs from the calculation sheet to the input sheet and linking them back to the desired location.
3. Yes, you are correct. Please go to the workbook and press Hide in ‘View’ menu or press shortcut (Alt, W, H) and then save the changes in excel.
Please let us know in case of any query.
I use the F1F9 macros but customized the input colour slightly(still light yellow). Your relocate macros are far too restrictive, it violates best practice because it uses tests that are inconsistent in the macro list and doesn’t deal with a user change–specifically your macro checks to see if its an input cell by testing RGB as one of o two colours—–eg ShadingLtYellow the code for Light Yellow is:
Sub ShadingLtYellow() ‘ Keyboard shortcut: Ctrl+Shift+I
‘check if active sheet is protected or not?
Call ChkProtecttionInd
If flag = 1 Then
flag = Empty
Exit Sub
End If
Selection.Interior.Color = RGB(255, 255, 175)
End Sub
And yet in the relocate code you test–
‘check whether correct input cell is selected or not
If ActiveCell.column ColInp And ActiveCell.column Rowinp Then
MsgBox “You have not selected the correct input cell. For: ” & vbCrLf & “Series input – Select the ” & Left(inp2, 1) & ” column cell” & vbCrLf & “Column input – Select the ” & Left(inp1, 1) & ” column cell”, vbInformation, “Input Relocation Alert”
GoTo Finish
End If
by embedding the RGB number in the code, which is also in the code for setting up the colour, you are akin to hard coding values into an excel formula.
If you are really trying to help and promote the macros as a tool, you should have decent coding that is not in conflict with the underlying best practise you are promoting in the excel sheets.
There are better more logical ways to handle this by simply setting up some constants
Hi, thank you for releasing a new version of macros. Please could you highlight the additions to the previous set of macros, and provide some explanations and examples of how the new shortcuts fit in into the broader FAST framework, meaning, what’s the context when they should be used? Thank you as always.
Hello,
Thanks for your question. The main changes in the macro are:
1.) *Shading colours:* The new colours that we used for shading are now lighter in colour than in the earlier version.
2.) *Converted 2 files into 1:* Earlier we used to use to 2 macro files (F1F9 Utilities 04s (hidden) & FAST Format and Navigation Macros 13b (hidden)), which is now converted to 1 i.e. (F1F9-keyboard-shortcuts-01p-1).
3.) *Date Shortcut:* Earlier we used “Ctrl + Shift + /” which is now changed to “Ctrl + Shift + L”.
4.) We have made a few fixes in the macro to make it more efficient in use.
Hope this answers your question.
Thanks for the update.
Q1: In previous version, we were instructed to store F1F9 macros in the XLSTART folder. Is the above instructions for storing the macro files better or is XLSTART still ok?
Q2: I also have a file “F1F9 Utilities Light …” in my XLSTART, should this now be deleted?
Thanks.
Hi Doug,
Yes, XLSTART is still ok.
And secondly; you can now store the current version in your XLSTART folder and delete any previous F1F9 utilities files – this one replaces both old files.
Hope this answers your questions.