Microsoft Excel Shortcuts

Make life simpler with these short cut keys in Microsoft Excel. Save Time & Increase Productivity!

Ever since I wrote about how to view all the formulas in Excel using a single click, I have been requested for more such Excel Shortcuts that save time. So here goes… There are some absolute gems that have been my favorite for years!

Shortcuts that work inside Excel Worksheets

  • Ctrl + F: Display the Find & Replace Dialog box, with Find selected
  • Shift + F4: Repeat Last Find
  • Ctrl + Z: Undo last action. Works for multiple levels
  • Ctrl + Y: Redo last action.
  •  F11: Create and Insert a Chart based on the selected data. Chart is added in a separate sheet.
  • Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
  • Ctrl + K: Insert a Hyperlink in a cell

Format Cells

  • Ctrl + 1: Format Cells Dialog box
  • Ctrl + 2: Apply or remove Bold formatting. Also works as Ctrl + B
  • Ctrl + 3: Apply Italic formatting to selected text
  • Ctrl + 4: Underline or remove the underline from the selected data. Also works as Ctrl + U

Apply Number Formatting

  • Ctrl + Shift + $: Apply Currency format with 2 decimal points
  • Ctrl + Shift + ~: Apply the General number format
  • Ctrl + Shift + %: Apply the Percentage format with no decimal places at all.
  • Ctrl + Shift + #: Apply the date format with full day, month and year showing.
  • Ctrl + Shift + @: Apply the time format with hours, minutes, AM or PM showing.
  • Ctrl + Shift + !: Apply the number format with two decimal places, a comma separator for the thousands place, and a minus sign for negative numbers.
  •  

    Want to Improve Your Excel Skills?

    Learn the Key Features of Excel Quickly & Easily, by Joining the
    Online Training on Basic / Intermediate Excel.

    To Get Most out of Excel, Learn the  Pivot Table techniques in our
    Pivot Table Masterclass Training
    !

 Shortcuts for Formulas

  • Alt + =: Auto sum formula is inserted
  • Shift + F3: Insert Function dialog box is opened
  • F4: Cycle through the four states of Relative referencing (A1) to Absolute referencing ($A$1)
  • Ctrl + Shift + Enter: Enter an Array Formula
  • F9: Calculates all the worksheets in all open workbooks
  • Ctrl + `: Shows all formulas. Toggle switch that shows and hides the formulas. If you can’t find the key, you can locate it just above the left Tab key. My favorite shortcut.
  • F2: Edit the formula or selection in any cell
  • F3: Paste a defined name into a formula. Using Range Names.
  • Ctrl + F3: Define a range name for a single cell or a range of cells.

Managing Workbooks

  • Ctrl + F4: Close the currently selected workbook window.
  • Ctrl + N: Open a New and blank workbook in Excel
  • Ctrl + Tab: Move to the Next Worksheet within the workbook.
  • Ctrl + Shift + Tab: Move to the Previous worksheet within the same workbook
  • Ctrl + F9: Minimize the current workbook
  • Ctrl + F10: Restore the selected workbook into a full window
  • F12: Save As diaglog box to save the workbook
  • F7: Do a Spell check in Excel
  • F1: Ask for Help ( internal Excel in built help pops up)
  • Shift + F7: Display the Thesaurus
  • Alt + F8: Displays the macro dialog box
  • Alt + F11: Pops up the Visual Basic Editor for editing the macros.

Hopefully these macros will help you save time and improve your productivity.

If you have any other shortcut which you find useful, do send me a note. I would love to hear from you. You can send it to vinai@excelchamp.net.

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


I teaches Excel, Advanced Excel techniques, and Dashboarding techniques in various countries. If you have a group of 10 or more participants,  I can come down to do a workshop for your group in your country – Vinai

Do You Use These Features of Microsoft Excel?

Most people hardly use the most useful features available in Microsoft Excel. Many are just using Excel as a calculator to do basic calculations like adding or subtracting numbers. This is a gross under use of Excel’s vast potential and feature rich functionality.

Do a quick check, and see if you use these advanced features of Microsoft Excel in your day to day work to improve your productivity and efficiency.

  1. Finding the Top 10 Customers or Finding the Bottom 5 Performers in the organization
  2. Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding – like =Salary – CPF + Allowance
  3. Exploit Pivot Tables to Summarize the data and slice & dice it in any way – like finding sales by product groups, or calculating productivity by department, or calculating sales by department by product by area.
  4. Write Macros to automate routine things that save you a huge amount of time – example creating different pivots, charts, tables, and doing complex calculations, or compiling the cost benefit ration, cash flow statement automatically.
  5. Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted, or all products with a net profit margin of less than 5% be highlighted.
  6. Sort the values in Ascending, Descending or any Customized sorting order – like sorting in order of Manufacturing, Accounts, Sales departments.
  7. Use advanced filtering conditions, and be able to filter data using multiple different criteria – like salary > 50,000 and sales <100,000 or Zone in East, West.
  8. Create fantastic charts that portray the given business situation perfectly. There are over 50 different types of charts to choose from, and each has its edge, advantages and a reason. Do you know when to use which type of chart – like two axis charts, stacked column and line charts, bubble charts, pie, slice of a pie etc.
  9. Create management dashboard that are dynamic, and provide a complete snapshot of the key business KPIs in the company – change the chart values at the click of a checkbox or change in a dropdown value
  10. Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
  11. Use Lookup tables to find any value or corresponding value from a table using advanced functions and formulas – find values that in the left of the looked up value
  12. Protect certain cells from data entry, and hide the formulas from other cells. Ensure that only certain values are keyed in the cells to avoid data discrepancies.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


This is just the tip of the iceberg… These are common things that can be done easily with Excel’s in-built features.

So how many of these did you know? And how many would you like to learn more about? Even if you learn one new trick in Excel, it will save you immense time and improve your productivity. Investing in Microsoft Excel Training bears fruit in no time, and is a no brainer.

Microsoft Excel is really extremely powerful. Each version of Microsoft Excel – be it Excel 2007, or Excel 2010 or Excel 2013, Excel 2016 or Office 365 adds more and more features to the already powerful dynamite of a package.

At ExcelChamp, we teach people how to leverage the maximum power out of Microsoft Excel in short training courses.

So what are you waiting for? If you would like to learn any one or more of such useful features of Microsoft Excel, just let us know. We provide online training through videos, which will show you each click, step by step, so you can master the new techniques easily, with exercises, and practical examples.

Go ahead, equip your team with the right skills. Get everyone on board to learn the basic and advanced features of Microsoft Excel, and Be Awesome in Excel!

Email to us to know more about Online Excel Training at ExcelChamp.Net.

Cheers,
VInai Prakash, PMP, ITIL, GAP, Six Sigma, Excel Guru

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

How To Count Cells With a Certain Value in Excel

There are several instances when you need to count the number of cells that contain a certain value.

For example, you want to:

  • Count if a product code is duplicated or not
  • Count the existence of a text string in a given range of cells

To do this, there are several different ways in Microsoft Excel.

Method 1: Use Sum & If Functions in Excel

=SUM( IF(range=”text”, 1, 0))

Note that this is an array formula, meaning that it must be keyed in a special way. Do not simply press Enter key to finish typing this formula. You must press CTRL + SHIFT + ENTER together.

Once you key in the array formula, it looks like below ( this formula searches for the value of 5 inn the cell ranging from A1 to C3. If it finds a 5, it counts a 1. All the counts are then Summed Up.)

=SUM( IF( A1:C3 = 5, 1, 0) )

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Method 2: Use the inbuilt Countif Function of Excel

 =COUNTIF(A1:C3, “text”)

Note that this is a simple and straight forward way. It is not considered an Array function. Just key it in, and press the Enter key.

Voila, it shows you the correct count.

There you are! Two Simple ways to count the number of occurrences of a value in a range of cells in Excel.

These above 2 methods work in all versions of Excel, on the PC and on the Mac.

Hope you will benefit from them!

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Vinai conducts workshops to enable all Excel enthusiasts in learning simple tips, tricks, techniques in Excel. Join a 2 day workshop in Singapore, or email us the city where you want to have the workshop conducted. We can

Finding The Second Largest Value in Excel

After my Advanced Excel workshop in Singapore, one of the participants, Lai Thim had a unique problem in Excel. Her boss was always asking her to show the Top 3 products, Bottom 3 countries, or Who came in the second or Third spot in the competition. She found it quite challenging to do this in Excel, and had to resort to sorting the data, manually going to pick the top 3 or bottom 2, and then copy and paste the values in the report. 

And this manual process had to be done each week, and each month, several times. So this was a big time waster. And she is not the only one… most Excel users are not aware of the simple functions that already exist in Excel, which can do the job in just a click.

So she asked me if there is a way to find the second largest value, or the third largest value given in a data set in Excel.

Join Vinai’s Data Analysis With Excel Training Course to Up Your Skills in Data Analytics.

Finding the Largest Value or the Smallest Value in Excel is Easy

You can easily find the highest value in any Excel data using the MAX Function, and similarly, find the smallest or minimum value by using the MIN function in Excel.

But try to find the Second highest value, or the Third Lowest in Excel, and these two MAX & MIN functions can not deliver this simple thing. They were meant for only the highest and the lowest values. This is extremely easy. Just use the Max() function. It will give you the highest value. Similarly, use the Min() function to get the smallest value.

However, to get the second largest value using this same formula, you can’t use Min() or Max() functions of Excel.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Using LARGE Function in Excel To Find Any Rank in Excel (including Second Highest)

Microsoft Excel has a hidden function, often unknown singe the late nineties, which can do the job perfectly. It is called LARGE.

Let’s say you have values from cell A1 to A5.

To get the Second Largest value, you could write the following formula in Excel:

=LARGE(A1:A5, 2)

This will give you the second largest value, without any fuss.

The format of the LARGE Function is =LARGE(array, k), and it picks up the Kth largest value from the array.

So to find the fifth largest country in terms of sales, you need to write the following in your Excel formula cell.

=LARGE(sales, 5)

What if you want to find the second smallest values? Well, we have a formula for that too.

Using SMALL Function To Find the Second Smallest Values in Excel

Just like Large, SMALL function will pick up the Second Smallest value from a range of cells. So you can use it for finding the Second Smallest, Third Smallest etc.

=SMALL(A1:A5, 3) would give you the third lowest value in the range of these 5 cells in Excel.

To find the second lowest values in terms of Sales, you can write

=SMALL(Sales, 2)

With this, you can find any lowest or highest, smallest or largest values in Excel easily.

Conclusion:

You can use SMALL & LARGE to pick the smallest, second smallest, largest, second largest etc. to your choice. They are pretty easy to use, and are already available in your version of Excel.

So if you use Excel 2007, or Excel 2010, Excel 2013, Excel 2016, Excel 2019 or even Microsoft Office 365, you can immediately use SMALL & LARGE Functions in Excel.

That’s it. Give it a try. This is a quick and dirty way to find such small or large values. These are not new functions… Have been there in Excel since 1995… 🙂

Have fun!

Related Posts:

  1. How To Count Cells With a Certain Value in Excel
  2. Learn the Multiple Ways to Count in Excel
  3. Simple Functions in Excel [Video]
  4. FREE COURSE ON PIVOT TABLES TO ANALYZE DATANo Signup is required. Click & Start watching Videos For Free and improve your Pivot Table Skills.

Cheers
Vinai Prakash
Connect With Me on LinkedIn: https://www.linkedin.com/in/vinaiprakash/

Vinai loves to share new and funky ways to find information from our data, using everyday Excel techniques.

Vinai also conducts in-house and public runs of his famous Advanced Excel Techniques, and Excel Dash-boarding Techniques in Singapore, Malaysia, Indonesia, Dubai, Hong Kong, and many other countries around the globe. Connect with Vinai at Vinai@ExcelChamp.net.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Additional Resources for Learning Excel & Become A Pro

  1. Top 12 Most Important Excel Functions You Absolutely Must Know
  2. Excel Beginners Guide
  3. Excel Essentials: From Beginner To Pro in Just 6 Weeks

Display the File Name & File Path in Excel (PC & Mac)

Many a time, it is good to Display the location of the file in Excel.

Not just the file name, but the entire path of the file. It is a good reference to make sure you are working on the correct file and is also good if you print out the Excel. With this option, the file path always gets printed on paper..

How to Write a Formula that will Display the File Path in Excel, automatically. 

One useful formula that can assist us is the CELL formula. I bet you didn’t know about this in-built Excel Function…

The CELL function can help us to display the address, colour, format, type or width of any cell.

The CELL Function takes a maximum of 2 arguments, and only the first one is mandatory.

=CELL(info_type, [reference])

So, if you write =CELL(“filename”) in any cell, you will get the Full Pathname of this Excel file, along with the Sheet Name.

For example, I got the filename displayed as below:

D:\[email-blasting-migration.xlsx]Sheet1

According to the Excel Help, the CELL function  has the following arguments:

  • info_type Required. A text value that specifies what type of cell information you want to return. The following list shows some of the possible values of the info_type argument and the corresponding results.
    info_type Returns
    “address” Reference of the first cell in reference, as text.
    “col” Column number of the cell in reference.
    “color” The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
    “contents” Value of the upper-left cell in reference; not a formula.
    “filename” Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.
    “format” Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values.

Display only the Excel File Name, without the Sheet Name

To only print the File name, you can use this long formula:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Keep in mind that the file should be saved somewhere. If you just open a new workbook, and try these formulas, you will get a #VALUE ERROR.  This error will disappear automatically once the file is saved, and the error will be replaced with the correct full file path name, and file name.

This formula to display the filename in Excel also works on the Mac. Any Excel for Mac version, including Excel X for Mac, to Excel 2004, 2008, 2011 for Mac also work fine in displaying the filename.

For example, for a file on Mac with the path HD:Users:local:Desktop, the formula returns
HD:Users:local:Desktop:(filename)Sheet1

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Displaying the FilePath only

While writing macros in Excel, you may need the file’s path only, to pick up other files, or for any other reason. Just use this to get only the file’s path in Excel

=LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1),1)-1)

In this formula, we remove the Sheet Name, which starts with the [ character.

How To Display Filename & File Path in Excel Worksheet Header or Footer

If you wish to display the file name and/or file path in the Excel sheet’s header or footer, then it is extremely simple. Simply go to the Ribbon. Choose Insert > Header & Footer

Excel File Name, File Path

All you need to do is click on the File Path, File Name icons.

Alternatively, if you want to type it yourself, simply type the following in the header or the footer in Excel:

&[Path]&[File]

Use &[Path] for only the File Path, and &[File] for only the File’s name.

Hope it helps you in your life with Excel.

Now you can confidently display the filename and the file path in Excel

With the File Name of Excel files visible in the header, with the File Path, you won’t  lose a file in the wrong location & find what you are looking for easily!

Do let me know if you need any other functions in Excel, or if you are facing a problem in Excel. We can help you to solve everyday issues in Excel easily at ExcelChamp.Net.

And subscribe to the Excel Tips Newsletter for more such tips to improve productivity in Excel.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Cheers,
Vinai Prakash

Vinai is the founder and principal trainer for Microsoft Excel Tips and Techniques seminars all over the world. To book Vinai to conduct an Excel training at your office for your company, do email to vinai@excelchamp.net.