5 Excel Secrets You'll Be Embarrassed You Didn't Know

[@ExcelCampus]

Secret #5 seems to be the most popular. Question for you... Are there any time consuming Excel tasks that you'd like to automate with a macro? Leave a comment and let us know. 👇🏼 I'm working on another video on the macro recorder and want to include examples that would be helpful for you. So, your idea or question could be featured in the video.

[@florenciaray5954]

Make a macro about how to keep the header row fixed on a sheet even if we are scrolling down so that we don't have to go all the way up to check the headers again and again while entering data. Because if we do so there surely is a chance of making mistakes. (I'm a beginner and came across this problem while doing a simple task)

[@stephanebarret2517]

@@florenciaray5954 Hi Florence, you really don't need a macro for that. Put your data into a table (an actual Excel one) and when you scroll down the column letters will be replaced by your table headers! Magic 😁 (and you can see that at minute 9:20 of the video checking day 5 is present in the table created with Power Query)

[@c17nav]

How about a merge cell macro that mimics Word's merge cell capability, which does not discard data that is not in the upper left cell? And a counterpart macro that splits data across cells?

[@c17nav]

@@florenciaray5954 Welcome to the Excel world. The program has the built-in Freeze Panes capability that creates header rows and columns. Access Freeze Panes on the View tab.

[@lareh5501]

@@florenciaray5954 Probably easier than a macro for that is to use one of Excel's "Freeze Panes" options (i.e. freeze top row OR freeze 1st column OR freeze whatever's directly above & to the left of the currently selected cell. From the ribbon, go to View tab and right click on the "Freeze Panes" option, then select the "Add to Quick Access Toolbar" option. FWIW, I like to show my Quick Access options BELOW the ribbon, that way more icons can appear without running into the file name AND they're available regardless of what ribbon tab is selected.

[@ryan19899]

Crtl Shift End instead of down arrow was new for me. Thanks Jon!

[@ExcelCampus]

You're welcome. 😀

[@corvinns]

Same here. But meanwhile I like the table approach more: Ctrl+A, Ctrl+T, then shift+space for rows and Ctrl+space for table column

[@davescellphone5376]

Same

[@techtricksgh]

Really, then you have learnt a new trick

[@kellyboyd295]

Thanks Jon! I've been scared of PowerQuery - but you made it sound super easy! I'll definitely be looking into it more.

[@dianebucciero7728]

The power query to combine files contained in a folder ...... I found this feature on the web a couple days ago, so still new to me, your video just cleared up a sticking point and added more detail. SO simple and will save me so much time and frustration! I will be watching more of your tutorials! Thank you.

[@ExcelCampus]

Glad to hear that, Diane! 😀

[@JanBolhuis]

Thank you for this one. Although I know a lot of shortcuts, I always find it interesting to watch these kind of videos. For just 17 minutes, I learned one new shortcut/trick - Ctrl Shift End - in case of blanks. An amzing one. Thank you. It will save me more than 17 minutes in the future :)

[@ExcelCampus]

Thanks so much, Jan! It will take about 40 uses of the shortcut for you to recoup your time. That's assuming you use it on an average of 10,000 rows that take 26 seconds to select. 17 / (26/60) = 39.23 Lot's of assumptions in that equation, but I love to geek out on this stuff. I'd love to know how many thousands of times I've used that shortcut alone. And I think it's awesome that you thought about the time you will save in the future. So good! 🙂

[@rexbk]

Ctrl+Shift+End is a new thing for me. Thanks

[@johnfox9780]

As always Jon........AWESOME! You always speak and describe things so well and at just the right pace for me. There's never any time wasting or 'padding' with you, that's why you're always my go to for excel. Thank you so much for sharing. Regards John (Inverurie, Scotland)

[@ExcelCampus]

Wow, thank you for your feedback! 😀

[@amithbsbs629]

Shift control end is new for me when there are multiple blanks in column and used to do manually. Now i am pro after watching

[@JudahPeters-m6h]

Great excel tutorials. I have learnt a lot. The most important was the macro. This will save me a lot of time and make my work super easy. Thank you so much Jon.

[@ExcelCampus]

Glad it was helpful! 😀

[@philhersh]

Awesome! Double clicking on the fill handle was something I learned from your video. Super useful.

[@JohnAdair-k7j]

When I have a select enough rows that I can no longer see my formula, then my favorite way to keep that range selected and "scroll" back up to the formula is while that to just hit Ctrl + Backspace. It's fabulous!

[@gagreeneeee]

if you go to File, Options, Advanced, and Untick Allow editing in Cells, then you will always edit your formulas in the formula bar at the top of the sheet.

[@Adam_K_W]

Great work as always Jon. At my company, personal macro workbooks have been banned/locked out by IT Security. So far, a workaround is to convert that same workbook to an Excel Add-in and then set it to load on start up. I've got my own personal ribbon with icons linked to the functions in that Add-In and also custom icons on the Excel shortcut dropdown list so i can get to them either way. If you put together a video on the Add-In approach for personal macros, I'm sure it would be popular.

[@DavidWinchell]

I sure wish I had found you much earlier, but I'm glad to have found you now. Thanks!!😀

[@EJRyder]

I use a very similar header row macro for my work that includes the filename (=CELL("filename",A2) when it is printed out so that I can find the file using the printed output. It is useful to have a blank row below the header if you use arrays with CurrentRegion.

[@veronicagonzales3114]

for my current job, i am most interested in Combing Files. I need to watch it again. thank U

[@ExcelCampus]

You are welcome! 😀

[@keithM7]

Thanks for the shortcuts, Jon! They’ll be really helpful at work. I’m having trouble with Excel though-it’s asking for a product key. Does anyone have any idea where I can get one?

[@Ann_779]

Hey Keith! I got my Excel product key from BNH Software and was able to use it without any problems!

[@MarkUSAfreedom]

@@Ann_779 Hey Ann, I have a question. Does this key work for all the Office programs, or just for Excel?

[@Ann_779]

@@MarkUSAfreedom Hello Mark! It's only for Excel. You can find the product keys for the other programs on the same website. Hope it helps!

[@keithM7]

Thank you, Ann!! It worked perfectly, and now I can use Excel without any problem!

[@Ann_779]

@@keithM7 You're welcome! I'm happy to help

[@ADTR0816]

Learning Analytics and these videos you post have been instrumental. Thanks!

[@bobbirowlett6728]

I have never created a macro in Excel, Access yes, but not Excel. Very interesting. You have expanded my knowledge on every point of your tutorial. I will use the quick keys for selecting ranges a lot. For the XLOOKUP function, is there value for naming headers and data in the formula? I know you most did absolute references. I really enjoyed this video. VERY HELPFUL!

[@joukenienhuis6888]

Thanks for the clear explanation of these often used functions! I knew them, but i think it is always good to keep reading it if you do not use Excel that much. I do have a question about PowerQuery. What if there already is file and you have to add the data of other files to it without creating a new sheet?

[@BrianCarey-e9l]

I appreciate you sharing your knowledge and skills. The videos were informative and simple to understand. I will use all 5 "secrets" to improve productivity. I like that you demonstrate the shortcut keys as you teach. Also, Xlookup was new to me.

[@Leggg]

Jon, a fabulously informative video with clear explanations. Many thanks. Sadly I think that I shall have to get a more up to date Office suite in order to be able to use the new functionality!

[@mgangat]

Thanks Jon for the informative video. This is excellent. My favourites are Power query and pivot tables that are time savers. Will learn power query in depth to make my life easier. Thanks again.

[@ExcelCampus]

Awesome! I'm happy to hear you're interested in learning Power Query. It's an amazing tool for data cleanup tasks.

[@JHatLpool]

Really nice demonstrations and descriptions here. MS Excel is so powerful now.

[@ExcelCampus]

Thanks! And yes! 💪

[@KathleenRyan-y8q]

All of these videos were extremely helpful - Thank you

[@Puja-d2i]

I have joined your channel today and this was the first video I watch it's amazing. If I go through all the videos I might become a pro

[@ExcelCampus]

Awesome! Thank you for your feedback. 😀

[@miguelmachin1870]

Thank you for your efforts, explained in great detail yet easy enough for a novice excel user. Great Job.

[@ExcelCampus]

Thank you for your feedback! 😀

[@JasonJPerry]

I really enjoyed the Macros but your explanation was great for all of your tips. I am a huge fan of keyboard shortcuts. Do you have a shortcut to automatically increase the column width to fit the data? I am currently using crtl+a and then alt+h+o+i, but I have to do this every time I need to review the data provided and updated from an outside source. I am currently unable to ask them to change their default column width so I am looking to possibly use VBA/macros to automate this step. I am interested to see if you've come across this challenge or if the aforementioned shortcuts are the fastest way to complete the action. Thank you again for teaching and sharing your knowledge, I really appreciate it.

[@ragulmarley4900]

Am new subscriber this one really awesome i learned new things especially ctrl+shift+end without knowing that i waste time now u saved everything :) do more video we support u kudos!!🥳

[@ExcelCampus]

Thank you for your feedback! 😀

[@skjones91199]

Great stuff! A guy on Reddit was asking about combining files today, and I was able to link this video! Thanks again!

[@ExcelCampus]

Glad it was helpful! 😀

[@Tom-kr2ek]

As always Jon appreciate your sharing your knowledge of excel your videos are always clear and easy to understand the sheet title layout was extremely helpful, I added an input box to mine so I could fill the title in as I created the sheets works perfectly thank you.

[@ExcelCampus]

I love that idea, Tom!! 🙌 Save a step in the process. The section on macros seems to be the most popular and I'll do some follow up videos on additional ways to use the macro recorder and modify the macros, including your tip on the input box. It' the InputBox function for anyone reading that wants to learn more.

[@francosilvestri123]

Thank you for the great video and hints. Do you save several macros in the same Module or each macro in a separate Module?

[@ExcelCampus]

Great question, Franco! I tend to put several macros in the same module. In my personal macro workbook, I group the macros based on their functionality (or what they do). For example, I have a module named mUnhideSheets that contains several macros for hiding and unhiding sheets. You can see an example in this video. kzbin.info/www/bejne/mZTOf6SLeqZ_mLM This organization allows me to find them a bit easier than putting all macros in one module. It also allows me to move a module to a different workbook if I want to use those macros in a specific project. I think there are pros and cons no matter which way you choose to organize your macros. And it's important to find a method that works best for you. One thing to note is that you can use Ctrl+F to find macros in the current procedure, module, or project. So if you don't want to organize them, search is another way to quickly find them. I hope that helps. Thanks again and have a nice weekend! 🙂

[@francosilvestri123]

@@ExcelCampus Thank you! I will follow your advice to better organize my macros! I didn't know about Ctrl+F. I will definitively use it in the future. Have a nice weekend too!

[@catchagoat]

As usual a very well structured video. I learnt a few new ways to do thing faster, especially the Macro.

[@ExcelCampus]

🙌 I'm happy to hear you learned some new things. Thanks for letting us know.

[@BlackThor15]

Awesome video, thanks for sharing! Do you have any that shows how to use a vlookup formula to compare 2 different dates in 2 separate spreadsheets and then to present the difference in days between one and the other? Thank you! 👍👍

[@ashfoster1045]

Great video, will be sharing this at work! Someone will make use of it 😊

[@ExcelCampus]

I'm so happy to hear that and thanks so much for sharing! 🙏

[@LindieKotze]

Very helpful! Compliments on how you present. So easy to follow. Appreciate the effort you put in

[@ExcelCampus]

Thanks Lindie! I’m happy to hear it helped and appreciate the nice feedback. 🙂

[@jodiwood2924]

Oh my goodness. Power query was the best for me. Thank you so much. On another note, every August we have a huge business meeting (approximately 800 in attendance) that I have to download all the data from the online registration and create name tags (among other things). I usually download all the info into a csv, then create a 'label' in Word, add the fields, .. you get where I'm going. Is there a quicker easier way? Any help would be appreciated. Thank you.

[@controlsgirl]

the recording macro might have been the most helpful for me but mainly because i already kknew the others. overall great video

[@ExcelCampus]

Thanks for the feedback! And I'm happy to hear you enjoyed the section on macros. We'll do more videos on that topic in the future.

[@johntucker2566]

I didn't know you could hit F4 to make references absolute. That's so handy. Thanks!

[@polyvg]

In my very first proper spreadsheet (using Lotus 1-2-3 on MS-DOS), not just trying to learn how to use them, I used lookups. It was only a modest size - but took over twenty minutes to recalculate. Needless to say, auto-recalc was impossible. Things have changed.

[@gizmovids]

Same! Lotus 1-2-3 as a summer intern in data processing. Had a fancy word processor with a "gold key" too! It's been a minute...

[@c17nav]

Creating a macro to insert and identically format a top row on multiple sheets is probably not necessary for most spreadsheet users. A different approach is to group a contiguous set of sheets by holding the Shift key, selecting the first sheet's tab and the last sheet's tab. All grouped sheet tabs will indicate that they are in active use. On an active sheet, insert the new first row and apply the desired formatting to the row. Entered text, etc. will be repeated on all grouped sheets. Note that an operation on a grouped sheet might be repeated on all sheets of the group if the operation is valid for all grouped sheets.

[@loriwangler3667]

Thanks, Jon! All so helpful, but I think I'll be using the macro function most.

[@HandelMcHandel]

5 for the price of one - one day only! Thanks Jon!

[@ExcelCampus]

LOL! Tell your friends! 🏃

[@dianneclower4911]

I love your efficient videos! Helpful

[@gardenlighting-barrysluite7505]

Awesome tips. Thanks heaps Jon. I’m so glad I found your channel, it was actually through Primal Video that I discovered your amazing channel and tutorials.

[@ExcelCampus]

Awesome! Thank you for your feedback! 😀

[@jasminetoro1244]

I want to understand more on the xlookup formula and the thought process to how the formula works. Example why =xlookup(C6?

[@aminamaradesa9778]

excellent video, very useful tips ( esp power query and macros) and very well explained.

[@ExcelCampus]

Many thanks for your feedback! 😀

[@TheWholeGrainBread_Real]

Ive watched a few of these videos and they are really helpful. I really appreciate the info you present!

[@ExcelCampus]

Thanks for your support. We really appreciate it. 🙏

[@robinlinneman1808]

I am going to benefit the most from Secret #5! Thank you!

[@ExcelCampus]

That's good to know. 😀

[@florenciaray5954]

Thank you.... These shortcuts are going to be really helpful in future

[@ExcelCampus]

Glad to hear that! 😀

[@jmacdono]

I am so glad I found your channel Jon. This is great and useful information!

[@ExcelCampus]

I'm glad that you find it helpful. 😀

[@franksarbu]

Thank you!

[@laurawahlbom5936]

Ty Jon! Can you run a second macro after the first? Have a situation at work, where I run a macro that creates 4 sheets, and then I create six other sheets in same workbook. Can I create/run a second macro on whole to give same formatting?

[@garypheasey]

Hi Jon. Just found your channel today and subscribed. Great tips. May I ask - what screen recorder do you use? You appear to fly through the steps and are able to annotate very fast. I'd love to learn VBA, is there a course you would recommend? Thanks!

[@mgrnmgrn]

Mr.Jon continue your great work to the community.Nice video to save the time.

[@ExcelCampus]

Thank you, will do! 😀

[@randyn629]

Thank you for the video. Always enjoy your videos, you have a great teaching manner.

[@ExcelCampus]

I appreciate your feedback. 😀

[@johnmccormick2883]

Great video… thanks for sharing! I was already onto most of the tips but the macro bar at the end looks helpful and I’ll check out your video on that! Cheers 🍺🍺

[@ExcelCampus]

Thanks for the nice feedback John! I'm happy to hear you'll be learning more about macros. One of my favorite Excel topics. 🙂

[@mscotty11]

I created buttons on my Quick Access Toolbar and assigned them to the most common macros I use. I didn't know we could make our own ribbon item though!

[@sallyho146]

Thank you. Which video that shows how to add my micro tab?

[@IvanCortinas_ES]

Yeah, excellent tips to increase productivity. Thank you Jon!!!

[@ExcelCampus]

Thanks so much Ivan! 🙌

[@fsimonpietri]

Excellent tips and tools. Straight forward explanation.

[@ExcelCampus]

Thanks, Simon! 😀

[@carriewenholz9126]

Could you create a style for the SheetTitle macro? I never use the preset styles as they were never quite what I wanted.

[@macbp41]

Hi, I'm a student and a beginner user of excel. The only device I have at home is a MacBook. What is the difference of Excel on MacOS compared to Windows?

[@joedi]

Surprised that I knew all of these. Doesn't mean that there isn't anything left to learn. Learning is never ending. My easiest shortcut was John's Excel hero add-in. Not sure that is sold anymore. But is pure gold.

[@MrSaemichlaus]

Click the first cell. Scroll down and click the last cell while holding shift. Unselect individual cells you don't want while holding ctrl. Same as when you select files or photos in windows explorer.

[@sumardjo]

Thanks about your knowledge and special thanks for sample file I can follow video step by step thanks very much

[@DallasMartin-s9w]

Thanks for these techniques

[@ExcelCampus]

You are so welcome! 😀

[@lazyreviewssupport9811]

Thanks for your newsletter bringing me here 😅

[@ExcelCampus]

Thanks for being a subscriber! We appreciate your support. 🙏 For anyone else reading this, there is a link in the description to join our free weekly Excel Pro Tips Newsletter.

[@jackcurl2005]

When recording macros, Excel doesn't necessarily do things the most efficient way, primarily because it's emulating you physically manipulating what you're trying to accomplish. But VBA can do things more efficiently. Primary example is always having to do a /SELECT/ on a range before operating. In all the VBA code I've written (hundreds of thousands lines or more), I've never used SELECT to accomplish any programming task except to be in a specific spot when I finish the process, or restore a user selection because certain VBA operations will clear the selection (and clipboard-which is not maddening at all). [On the other hand, SELECT is nearly mandatory for any SQL programming. 😆]

[@jackcurl2005]

All that to say: Recording macros provides a great start and certainly provides insight into the needed commands. But that's not necessarily the end of the task.

[@ExcelCampus]

Great points, Jack! I completely agree. The point of this video was to provide that start and introduction to macros. We do have several other videos on VBA. Here is a link to one of our most popular ones for anyone else that wants to learn additional ways to automate tasks with macros. kzbin.info/www/bejne/b4OTkJuVltSGd6c

[@PaulEBrownbill]

Thanks Jon, these tips are really good, especially about recording macros. Paul

[@ExcelCampus]

Thanks Paul! I appreciate it and I'm happy to hear the feedback on macros too.

[@kgotlaetsilemodieginyane7491]

Wow Jon... what a good short leaning video... I am ur new subscriber right away.

[@ExcelCampus]

Thank you! 😀

[@bettyjohnson1625]

Is there a way to select a group of cells in a column, then tab to the next column and have the same amount of grouped cells selected? For example selecting cells A1 thru A12 and tab over to have just B1 thru B12 selected?

[@Xman-lg7qg]

need a bit more about power query- how do i get more videos on this topic- btw my favorite

[@less_thanONEmin]

oh my , i was searching for a day and this is the best video

[@ExcelCampus]

Glad I could help. 😀

[@yyusof78]

Marvelous, very value added info

[@ExcelCampus]

Thanks so much! I'm happy to hear it. 😀

[@thecoursedoctor]

Love the cntrl + shift + end reminder, but now that I'm on a Mac we don't have an end button. figured out that shift + command + down arrow works if fields are filled in, but it didn't work when there are empty cells. Would love to know if anyone found the alternative when blanks. 🦋

[@leeching1]

The macro - how to use the macro to run an automatic pick for a list of purchase orders every day, based on min and maximum inventory levels, without an inventory management ERP system?

[@toniantrowers5922]

Did you get an answer

[@leeching1]

@@toniantrowers5922 Not yet.

[@lucianoriquet8552]

Very good. Nice job!

[@SergioJrVarquez]

very helpful!

[@williamkirkwood2070]

Really helpful!

[@mkhan-35]

Thanks my friend.

[@ronaldoliver1700]

Thank you

[@CarolynNguyen-sd9pr]

Great excell tips, very helpful! Thank you, Jon! =)

[@ExcelCampus]

Glad it was helpful! 😀

[@NovemberDelta]

Woa so macros.... I want to make custom macros for.my business for our internal forms and sheets. I need to figure that out!!!

[@cimbrow]

very easy and clear

[@lmh4186]

Love this.

[@laurengibbs8761]

macros is a great tool to now use thanks

[@ExcelCampus]

You're welcome! 😀

[@chrism9037]

Excellent tips, thanks Jon!

[@ExcelCampus]

Thanks Chris! I appreciate it! 🙌

[@hw7channel571]

Great work sir🎉

[@ExcelCampus]

So nice of you 😀

[@dinahany233]

thanks for informative tricks

[@SS-oz3eu]

Some really helpful tips 👍

[@josealvesferreira1683]

Very good, congratulations!

[@ExcelCampus]

Thank you! Cheers! 😀

[@yulfaemellya5142]

Great video. Thanks a lots

[@prischang3315]

Thank you so much, excellent and helpful.

[@ExcelCampus]

Glad to hear it was helpful! 😊

[@CraigIreland-q9l]

Jon do you have a keyboard shortcut sheet for Mac

[@ronalddcunha6136]

Hi Jon, Thank you for the keyboard shortcut tips. Just a doubt to clarify. When I enter CTRL+SHIFT+End instead of going to end of the Column, the Print Preview Page Pops out. Please could you help me how to resolve this.

[@ExcelCampus]

Hi Ronald, I'm not sure what is causing this. Are you using a laptop? Are you on Windows or Mac?

[@notawix8542]

thank youuuuuu 😊

[@KaziMaruf-ik6tk]

Rial life practical job is it. Thanks for sharing

[@ExcelCampus]

Welcome 😀

[@petr6617]

Unfortunately, the operation of the End key is usually unusable. It must actually be the last cell of the data range. In addition, Excel clumsily remembers the cells we entered (by mistake), so it often marks an empty cell somewhere at the bottom right. This behavior is also familiar to VBA programmers who have to reset the UsedRange. I'm afraid that this will make the End key as forgotten as the extended mode (F8) or even the slightly hated ScrollLock.

[@6c3333]

#6: Scroll Lock changes the behavior of the arrow keys :)

[@NawazSyed-kt3oy]

Thanks Jon You're Amazing 👍

[@vokisek]

ctrl+shift+end will not work in all situation. It will go to the last cell in Excel. so if there would be a value in cell e. g. AA1000 it will select everything from active cell to AA1000