Today is teachers’ day in India. I owe everything I learned to my teachers. Their powerful, insightful & inspirational lectures make me a better person.
Today I want to recruit more teachers. Because you can never learn enough.
So why don’t you come on board and teach me (and rest of our website visitors) something?
All you have to do is post a comment with an Excel tip, technique, idea or experience with us. It can be related to anything, from formulas to solver, charts to power pivot, VBA to keyboard shortcuts. Anything goes.
Lets go. I am all ears.
70 Responses to “Teach me (and rest of our community) something this Teachers’ Day”
Hi Chandoo!
Great initiative! Thanks to you too for this great website and the continuous and excited knowledge sharing!
We would like to teach how to think out-of-the-box and solve seemingly impossible challenges.
Here is an example - a simple chart-trick: how to combine bubble chart with line: use a trendline on a dummy series!
You can read the details here:
https://sites.google.com/site/e90e50fx/home/combine-bubble-and-xy-scatter-line-chart
Cheers,
The FrankensTeam
Hi Chandoo,
Great chandoo
This is very informative Kris. Thanks for the tip.
I have a small trick for logging your current time. Some of us work on hourly basis. So, we need to log in hour, date we have logged in. Use a the keyboard shortcut
Cntl+Shift+; - for current time
Cntrl+; - for current date
Happy Teachers Day Chandoo 🙂
First of all, thank you Chandoo for your awesome and inspirational blog! You have certainly taught me many things about Excel that help me on a daily basis.
Now, when I read 'shortcuts' in your post today about teachers' day, I was all ears, for I love to minimize the use of my mouse as much as possible.
Something that I need rather often is copying formulas and inserting them as values, or transposing values from horizontal to vertical or vice versa. Instead of going through the process with several mouse-clicks, I find it saves me a lot of time to use this shortcut:
Ctrl + Alt + V
This will give you a popup dialogue, and even there you don't have to use the mouse, but can use the underlined command keys to insert values (V), formulas (F), formats (R), or even transpose (T) your values etc. (Please note that I'm not entirely sure if these are the correct keys, as my Office is in German rather than English.
Once you have made your choice in this dialogue, just confirm with 'Enter' to special-paste your selection. Hope this helps some guys out there save some time.
Btw, while I'm at it: You can apply certain 'quick-formats' to cells with (CTRL + SHIFT + [1-6]), e.g. CTRL + SHIFT + 5 : Format cell as percentage.
hi.
after doing CTRL+C (for copy) you can use the key between ALT GR and right CTRL (the one that as an arrow). sorry, but i'm from portugal and i don't know the exact name of this key.
but i know that it does the same that the right mouse click.
so after CTRL+C, you can paste using (that key+V) instead of CTRL+ALT+V
CTRL+BACKSPACE - to return at selected cell
Hi Chandoo,
What a great way to honor teachers, not just in India, but world wide.
I would like to share a simple tip on how to tap into databases from Excel: http://www.youtube.com/watch?v=I1qvqMiGyhc
Best Wishes from Beyond Excel
Great idea, Chandoo!
I would like to share a tutorial on how to use the Pattern Property of Regular Expressions with VBA : http://dataprose.org/2013/12/scrub-your-data-expressively/
I will share my two favorite post on how to:
- Use Data Tables,
- Do Monte Carlo Simulations &
- Make Mandelbrots
all in Excel without VBA.
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
On what can be achieved using the incredible power of Named Formula
http://chandoo.org/wp/2011/07/06/3d-dancing-pendulums/
Thanks Chandoo for all you have done. Here are a few nuggets that I teach people to use at work:
In Excel 2013 only (I think) the =FORMULATEXT(cell) formula to have Excel automatically convert your formula to text. Great for those TEACHERS of Excel!
Make your Data Ranges into a Table (absolute lifesaver in my line of work):
- CTRL + T
Any/All of the CTRL + SHIFT key combos. I particularly like:
- CTRL + SHIFT + Up/Down/Left/Right - To select all cells in a column/row
- CTRL + SHIFT + [1-6] (As aforementioned by Chris)
- CTRL + SHIFT + A - To insert argument names into formula. This works great with User Defined Formulas because they don't have formulas tooltip box that default formulas have.
CTRL + Enter - To enter a value/formula of the active cell into all selected/highlighted cells.
F4 - While in the formula bar to toggle between Relative and Absolute cell references
F9/CTRL + Z - F9 While in the formula bar to evaluate a portion or all of your formula(s). The CTRL+Z "undo's" the evaluation and puts your selection back into "code".
Here is a comprehensive/exhaustive list of Excel (and other) keyboard shortcuts:
http://www.asap-utilities.com/excel-tips-shortcuts.php
-Scott
A quite simple thing I've noticed is that often people make beautiful charts/dashboards/etc., but the sizes of their charts are SLIGHTLY off and the alignment of the charts is SLIGHTLY off (and it drives me crazy!). When you click on a chart or shape, use the FORMAT toolbar and on the far right size is the height and width of the shape. Use this to make them all the same. Then, once the size is set. Use the ALIGN function on this same toolbar to align their centers, left/right sides, etc. It makes things look so much neater and tidier after all that work building the workbook. My apologies if this is too easy for this post but I often find this overlooked and a nice finishing touch. PS - you can do the same in PPT if you've pasted your charts into a presentation already.
@Christina
I also use the ability to Snap a Chart to a Cell Boundary using Alt and Drag
Size the Columns/Rows to the size you want the charts to be
Select a Chart
Hold Alt and Drag the boundaries of the chart
Note that they snap to the Cell's boundaries
Repeat for other charts
You can now resize the columns/rows and all charts will adjust
Hi Christina,
Really appreciate your comment.. you can even drop the alignment tools in quick access toolbar to make them even more handy.. If you wish you can see a short video here
https://www.youtube.com/watch?v=KlkMrxpRzk0
Thanks Hui and Chandeep! I hadn't thought of using snap to grid and I completely forgot about my quick access toolbars. I've added them there too which is great! So glad I posted about it. Thanks again and thank you for the video!
You might have already used the XNPV built-in Excel function for calculating the present value of a stream of cash flows with dates. How about XNFV for future value? Wait a minute. There is no XNFV.
Here are two formulas to calculate both present and future values:
PV=SUMPRODUCT((1+APY)^((MIN(MyDates)-MyDates)/365)*MyData)
FV=SUMPRODUCT((1+APY)^((MAX(MyDates)-MyDates)/365)*MyData)
The difference is that the present value uses the starting date and future value uses the ending date.
P.S. the workbook is available at http://pistulka.com/other
Hi don the workbook not available the site is down I have some of your excel file please upload all I like your work very much
Keyboard shortcuts are the best! Here are some I use hundreds of times a week:
A-Z sort - ALT,H,S,S
Z-A sort - ALT,H,S,O
Multiple sort window - ALT+7
Filters - CTRL+SHIFT+L
Protect/Un-protect sheet - ALT,R,P,S
For anyone who uses Julian dates daily, here's a formula that has save me a lot of reference time! First digit is the year, then 3-digit Julian.
=RIGHT(YEAR(TODAY()))&IF((TODAY())-(DATE(YEAR(TODAY()),1,1))+1<100,"0"&(TODAY())-(DATE(YEAR(TODAY()),1,1))+1,(TODAY())-(DATE(YEAR(TODAY()),1,1))+1)
Cheers!
@Chad
This is much shorter:
=RIGHT(YEAR(TODAY()),1)&TEXT(TODAY()-DATE(YEAR(TODAY()),1,0),"000")
Chad's solution seems to work, but yours throws a #Name? error Hui...
@S. Mark
You may need to retype the " marks in my formula manually as the WordPress has replaced them with similar looking characters that actually aren't " marks
Paste Values Wiggle:
To convert a column from formulas to the resulting values, select the column, then moving the mouse pointer to the edge where it turns into a 4 pointed arrow. With a right click, wiggle the column one position to the left then back to where it was. When you let go of the mouse button, select Copy here as Values.
Awesome!!
We teach this with three separate steps -
now, this is a time-saver!
Thanks for giving us this trick John.
Dear John
Thank you for valuable tip.
regards
Hi John,
cool trick, I liked it instantly. Saves a lot of time and hassle about the daily office routines.
Thanks, dude!
This is a good one:
http://www.mrexcel.com/forum/excel-questions/803451-excel-string-need-divide-cap-words-uncap-words.html#post3928535
It is a simple tip, but this keystroke shortcut saves time when you need to enter today's data.
Keystroke to enter today's date:
CTRL then "+" then ";"
Whoops, I now see that Krishna posted the same tip. 😉
Before you start seeking some super function, it may be better to use simple mathematical operators: +, -, x, /, ^, =, ...
Hi Chandoo,
to honour the teachers' day and honour your country
and in gratitude for all your good work,
just thought to post you an old blog entry called
"Learning is a self organising system"
http://www.2013.net/blog/index.php?/site/comments/learning_is_a_self_organising_system/
The youtube video is a bit long and the pronunciation is a bit ... well ... typical 🙂 ; but surely some of the insigths are golden.
best regards
Thanks for the opportunity Chandoo. My teaching moment has to do with my love affair with my Mac. That's right, I'm one of the guys that thinks Excel 2011 is Awesome.
But sometimes I have to use Excel 2010 on a Windows 7 PC.
So I have to remember lots of extra keyboard shortcuts.
I use a lot of absolute references. In Excel for Windows you simply press F4. However, if you press F4 on a Mac you activate Launchpad. Which takes you out of Excel and causes frustration. You have a few choices.
1) In OSX Keyboard preferences you can turn on standard function keys by simply checking the box. Which is great however Excel 2011 is already preprogrammed to ignore F4.
2) You can manually enter $ into your cell references when typing your formulas. Which is not fun. Typically I forget to do it. Then manually adjust my formulas afterward.
3) You can simply remember COMMAND + T is the Excel 2011 toggle for absolute references.
Thank you, thank you, thank you! Any other Mac shortcuts?
Yes, here's another Excel 2011 tip. Excel 2011 does not understand "Pivot Charts". It obviously understand Pivot Tables but not pivot charts like in Excel 2010 and 2013.
Sucks huh? This can be a blessing in disguise. Many times I want to chart only portions of a pivot table. Why, you might ask?
There are cases when I need to make charts based on pivot table data. Also there are many cases where I create complex pivot tables vs. multiple simple pivot tables.
(The following information assumes I don't make changes in the pivot table - changing the pivot table breaks the charts)
In Excel 2011 I can chart a potion of a pivot table. In fact, I can create multiple different charts based on different values in the pivot table, without reorganizing the data in the pivot table, filtering, or sorting. I also have infinite flexibility with the charts such as dual axis, combining multiple chart types in the same chart, and unique formatting that is difficult in a Pivot Chart without dramatically altering the original pivot table. I can even "Switch Rows/Columns" without affecting the original pivot table. It's very cool.
Having said all that, I'm sure the next OSX version of Excel will understand Pivot Charts and my tip will be relegated to the past.
One more quick one. If you are working with named ranges, or would like to do so.
In Excel 2010, 2013 the "Name Manager" is located in the Formulas ribbon. A nice logical place.
In Excel 2011 there is no "name manager" button. However you can accomplish the same goal by clicking on "Name" on the "Insert" drop down menu. Your options are "Define", "Create", "Paste", and "Apply".
I don't know that I'm particularly well-suited to teach people, but I certainly gained a lot from the Chandoo Excel Master Class in Columbus, Ohio last summer. So in the interest of giving something back, I offer this custom function I created.
I've seen several situations where people use INDEX/MATCH to simulate a left-looking version of the VLOOKUP function. And, although I can use that formula, it was ultimately easier for me to create a UDF.
The syntax for its use is as follows:
=LLOOKUP(value to match, search range, number of columns to the left to search)
[code]
Public Function LLOOKUP _
(fval, rngSrch As Range, intLeftCol As Integer)
'Function created 25 Jul 2013 by Jason B White
Dim lngStartRow As Long, lngEndRow As Long
Dim rngLeft As Range, intStartCol As Integer
intStartCol = rngSrch.Resize(1, 1).Column - intLeftCol
lngStartRow = rngSrch.Row
lngEndRow = rngSrch.End(xlDown).Row
Set rngLeft = Range(Cells(lngStartRow, intStartCol), Cells(lngEndRow, intStartCol))
LLOOKUP = WorksheetFunction.Index(rngLeft, WorksheetFunction.Match(fval, rngSrch, 0))
End Function
[/code]
Range names and INDIRECT() function
Here is a powerful combination of INDIRECT() function and Range names.
=SUM(INDIRECT("A2"))
By entering valid and existing rangename in cell A2 the above function will add relevant cells.
The above approach combined with charts, can create dynamic charts.
Cheers
KG
In Quick Access Toolbar, click commands and select all commands in the first listbox. In this list there is a button named, "Toggle Read Only". This button let's you change status of file read only to editable mode. This very useful command especially you are working with files that are also using by more than one person.
Have a nice weekend !
Ahmet
Here is the interesting array formula. For me it is like miracle but for you maybe just another boring formula :-).
I call it UNIQUE: it tells you how many unique items is in the range.
=SUM(1/(COUNTIF(B2:B100;B2:B100)))
dont forget (CTRL+SHIFT+ENTER).
I think it should be
=SUM(1/(COUNTIF(B2:B100,B2:B100)))
Try move / fill cells by using Right-Click drag and drop. 🙂
This is an example:
http://wmfexcel.wordpress.com/2014/08/23/move-cells-by-right-click-drag-and-drop/
Credit goes to Debra Dalgleish of Contextures.com
I import a lot of text files to excel. I found that I can usually just drag the file to an already open excel file. This saves having to go through the three step import dialog windows.
Great tip Angela. Very useful.
Thanks
yes...teachers open the gate of knowledge,love and cares.thank you all the teachers
tnx.
This one was from a video by Mike Girvin (Author of "Control + Shift + Enter"). When writing vlookup formulas a sorted list is quicker and more efficient for Excel to work with. If working with unsorted lists and using the 'false' argument (last argument in the formula) you can use '0' (zero) instead of false. Save yourself four keystrokes. Yippee!
When making drop down lists, use a named range for the list content and when you get to enter the cell references in data validation, just press F3 and al your named ranges will appear!
1. To select visible cells only, press Alt + semi colon
2. To select cells with comments, press ctrl + shift + O
This is my favorite Pivot Table tip:
http://youtu.be/Y4lA5blmPmw?list=UUMaVSMuAqV5j9WRdUz9UQfw
Here is the correct video:
http://youtu.be/Y4lA5blmPmw
Hello Chandoo! Thanks for all the great work in trying to make Excel easier! I have a tip I use all the time, and teach people on a constant basis. Chart format copying. If you have Chart A formatted perfectly, and you want Chart B formatted the same way, here is the shortcut. Click on Chart A and do a Ctrl + C ( standard copy ). Click on Chart B, and go to Paste Special, Format Only. Bingo! You have saved yourself time in trying to format Chart B.
To copy the value in the cell above, use CTRL + ' (apostrophe). This works in Access too.
Be careful when using this in any cell that has a formula in it as it will copy the formula exactly (As opposed to a relative formula changing)
Wow, this is a great thread. What I found a great tip for using with sub-totals.
If you are using subtotals but just want the summarized values, do the following. Create your subtotals as usual. Below this I have given instructions on how to do sub-totals if you don't know how to do it.
1. Click on 2 (top left hand side) - this gives you the summaries
2. Click in your list.
3. Press f5 - special - Visible Cells only
4. Press Ctrl and C to copy
5. Go to another part of the spreadsheet - Ctrl and V to paste.
6. It will only copy the summarized values..
Creating sub-totals
1. Sort your list in the by what you want to subtotal e.g. if you want to get the subtotal of customers by city, sort by city first.
2. Click in your list. Choose Data - Subtotal
3. At each change in....add City (for example)
4.Add Subtotal to - choose number field (usually) e.g. sales
5. Click OK
To add Column or Row or Cell - "Ctrl"+"+"
To Delete Column or Row or Cell - "Ctrl"+"-"
To select Row - "Shift"+"Space Bar"
To select Column - "Ctrl"+"Space Bar"
This is a small collection of shortcuts and productivity tricks.
- F11 creates a quick chart in a new sheet. It's useful to spot unusual patterns in the numbers. ALT F1 creates a chart in the same sheet. To make the F11 functionality even better, once the chart is made we can copy and paste new items in a classic way, selecting ranges, then CTRL C, CTRL V (pasting directly inside the chart). The default template chart can be changed so as to have a custom chart.
- CTRL SHIFT O (the letter) selects cells with comments
- ALT Down arrow shows the options when we are in a cell that contains Data Validation or a filter. But even better, it also shows data from adjacent cells when working with data
- CTRL 1 opens the Format Cells Dialog box. CTRL SHIFT F opens it with the Fonts tab active
- The difference between FIND() and SEARCH() is how they deal with case: FIND() is case-sensitive and SEARCH() is not
- To move charts using the arrow keys simply hold down CTRL and left-click the inside the chart. You’ll get 4 small circles in the chart corners that indicate the chart object is selected and can now be moved
by pressing the arrow keys.
- To move a chart and align it with cells, hold down ALT key while moving the chart
I just learned this and came back to share it
- How to fix cells that appear to be blank but are not: replace all blank cells with a word that doesn't exist in the spreadsheet and then replace that word with nothing. Blanks cells that were not blank in reality will be blank again http://goo.gl/ushPq8
Hi Chandoo,
First of all, Happy Teacher's Day to you (belated). My tips goes like this:
1. =CHAR(ROW(65:65)), this works well to list the alphabets in excel
2. =ROMAN(ROW(1:1)), this works well to list the roman numbers
3. We can Embed Table List in VLOOKUP (to make table dynamic), This can be done in usual way of how VLOOKUP does and select the table range in the formula (using formula bar) and press F9 which converts to array. This will stay even if the referred table is removed.
Above all, How to find your name using Excel Column Headers? My Name is ABHILASH, So, I will retain column A and column B and hide C,D,E,F,G and retain H and I and so on till I get last letter H. So Excel will display your name using column headers.
Few more on my blog http://www.exceltoxl.com
Thanks
[…] was Teacher’s Day this month, and Chandoo asked his readers to post their favorite Excel tips, in honor of the […]
While working with automated emailing, after believing I had everything set up I had a user try to use the Application only to have Outlook Setup start running. This was because the User did not have a Company Email address set up.
Since the system would be used by both those with and without email accounts (and the emails were more optional than critical) I needed a way to find out ahead of time whether or not an account existed, and if not, then skip the email part of the code.
This little baby does that trick by checking the Registry:
Check if User Has an Email Account:
Option Explicit
Function ProfileConfigured() As Boolean
Dim ws As Object
On Error Resume Next
Set ws = CreateObject("WScript.Shell")
ProfileConfigured = ws.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\DefaultProfile") ""
End Function
Hi Chandoo,
congratulations and Im really thankful to all teachers.
I love this simple shortcut:
Alt + H +E + A
It clears all formats.
Hi Chandoo!!!
I love your website! I'd like to learn more about interpreting a trend line equation in a line chart. Do you have a post on this?
Thanks much!
@Mkellyb
Have a look at:
http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/
http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/
http://chandoo.org/wp/2011/01/27/trendlines-and-forecasting-in-excel-part-3/
You can use alt + shift + arrow to the right (or left) to (un)group rows. This is a lot quicker than grouping rows using the buttons in the formulas tab.
Thanks for the wiggle way of converting formulae to values
isnt right click paste paste as values just as fast?
to fill several cells contiguos or not with the same information highlight all of them type the info and hit enter.
I will use this often! Thank you!