Excel Navigation Shortcut:  Double Click Your Way to Faster Spreadsheet Navigation

Excel Navigation Shortcut: Double Click Your Way to Faster Spreadsheet Navigation

Rss_feed_sub

This is an incredibly easy and simple tip and an Excel navigation shortcut that I quite honestly had no idea existed until just recently.  I’ve always used Ctrl + left/right/down/up arrow to quickly get to the top/bottom of my current columns or start/end of my current row.  That being said this tip is great if you are a heavy mouse user and just love to click things- so here it is. When you are on any cell, you can double click the top/left/bottom/right of your cell to get literally to the beginning or end of that row or column.  You need to make sure that you have the 4 point cursor available when hovering over either of the sides of your cell. Here is  a quick gif image to show you how this works.  Note that the image below is only showing you how to navigate from top to bottom (and from bottom to top) of a column but you can you with the left or right hand sides of the cell to go to the beginning of the row or to the end of the row.

 

EXCEL QUICK TIP DOUBLE CLICK CURSOR TO JUMP TO TOP OR BOTTOM CELL OF COLUMN

 

I hope you enjoy this tip as much as I did and I already forget where I came across this but it could be a life changer for some of you and I hope it is.

Until the next time!

 

 

 

29 Excel Skills And Tips You Need to Learn – A Tutorial For Beginners

29 Excel Skills And Tips You Need to Learn – A Tutorial For Beginners

I first started really using Excel when I moved out of my first position right out of University into my role as an Inventory Controller for a company that sells products in the mining industry.  Now that I’ve settled into my even newer role with the same company as a Purchasing/Planning Supervisor, I find myself using Excel more and more everyday to help me perform tasks that range from very simple to some very complex ideas, concepts and dashboards.

That being said,  there are a key group of things that I seem to use almost on a daily basis that have saved me countless hours.  For this post I decided to sit down and really think about some of the things that I use daily that are easy and quick for beginners to learn that I now wish I would have first learned when I had originally started out.  I’m not going to lie, some of the things I’m going to show you are very simple and easy things to do and you may even say hey, I already knew that, but I know for a fact that there are people out there who are just starting out that haven’t really got there hands dirty in Excel and that these tips will help them move into learning it just that much quicker.

Let’s start out with a very quick point form list.  This is simply a point form list with the topics that will be covered with a quick reference guide to the shortcuts that you can use to apply the functionality.

 

29 Excel Skills and Tips You Need To Learn

 

TIP DESCRIPTION SHORTCUT KEY
1 ESC to launch spreadsheet (Excel 2013) ESC
2 Enter text on a different line in one cell ALT + ENTER
3 Fixing the width of rows/columns and making them all the same size (if you want) or wrapping text DOUBLE CLICK BETWEEN ROWS/COL
4 Entering Data into multiple sheets in a workbook at Once CTRL + SELECT TABS
5 Shortcut to Navigate between worksheets CTRL + PAGEUP/PAGEDOWN
6 Navigate left/right within cells without using your mouse during data entry SHIFT TAB/TAB
7 Navigate up/down within cells without using your mouse during data entry SHIFT ENTER/ENTER
8 Copying Data above or to the right at the stroke of a couple of keys CTRL + D / CTRL + R
9 Using Excel Auto Fill To Finish your data entry Job – Bottom Right Drag-Drop DRAG BOTTOM CORNER OR DOUBLE CLICK TO FILL DOWN TO WHEREVER RANGE COLUMN ENDS
10 Copy Paste Excel Functions (using the different paste options available to us) ALT, H, V (THEN OPTION)
11 Dragging content from one cell to another DRAG
12 Inserting a row or column and deleting a row or column CTRL + (+) / CTRL + (–)
13 Creating a table in Excel CTRL + T
14 Quick way to apply common formulas to a range of cells and using name ranges ALT, H, U, S(SUM)
15 Show all of the formulas in a spreadsheet CTRL + ` (KEY BESIDE 1)
16 How to get to the end/start/right/left of your data range in an instant CTRL + RIGHT/DOWN/ (SHIFT TO SELECT)
17 Select all of your data in a spreadsheet – Fast CTRL + A
18 Create a new name range in your spreadsheet CTRL + SHIFT + F3
19 Link something to another page or document – Hyperlink CTRL + K
20 Freeze Panes – How to freeze the area to display your header and data that you need ALT, W, F
21 Group and Ungroup data ALT + SHIFT + LEFT (UNGROUP)
ALT + SHIFT + RIGHT (GROUP)
22 Text to Columns ALT, A , E
23 Remove duplicate records in a data set ALT, A, M
24 Format Painter ALT, H, F, P
25 Hide tabs of your workbook or cells
26 Removing the gridlines from the spreadsheet ALT, W, VG
27 Quickest way to open a new workbook CTRL + N
28 Navigate between multiple workbooks CTRL + TAB
29 Saving the workbook & Print the workbook CTRL + S (CTRL + F12) – CTRL + P

 

TIP #1 – USE THE ESC KEY TO EXIT THE MAIN SCREEN

  1. Open up Excel.
  2. Type the ESC key.
  3. You are automatically brought to the spreadsheet.

 

ESC- KEY SHORTCUT

TIP # 2 – USE ALT + ENTER TO CHANGE LINES WITHIN A CELL

  1. Enter text into a cell (in our case How to Video:)
  2. Type ALT + ENTER.  This will allow you to change lines within the cell.  Do this twice for double spacing.

 

Alt_Enter

 

TIP # 3 Fixing the width of rows/columns and making them all the same size (if you want)

(1) Determine which column(s) or row(s) you would like to adjust.  If multiple, highlight the columns or the rows.

(2) If you want to tighten up the column as close as possible to the cells in the column or row that have the most text, double click between any row/column (or the row or column) that you’ve selected.

(3) If you would like to adjust the column(s) or row(s) to a specific/fixed width, you can do this by dragging left or right for columns or down or up for rows.  You’ll notice that you can actually see the width or height being displayed as you drag the row/column divider.

Cell Width-Height Adjustment

 

TIP # 4  Entering Data into Multiple Worksheets at Once

(1) Use control + left click to select multiple tabs/worksheets in your workbook.

(2) Begin filling out one of the worksheets as you would normally and then check the other tab to see that your formulas, formatting, data etc… has also been duplicated and copied over to all sheets/tabs selected.

 

multiple_tabs_data_entry

TIP # 5 Use Ctrl + Page Up or Page Dn to Navigate between Worksheets

(1) Let’s say you have multiple tabs on a worksheet.  From your worksheet tab, type Ctrl + Page Dn.  This will bring you to the worksheet to the right of your current sheet.

(2) Now type Ctrl + Page Up and you will then move back a tab.

 

Switch_Between_tabs

TIP # 6 Use the Tab and Shift Tab Key to Move Back and Forth Between Cells For Easy Data Entry

(1) Type the tab key to move from your current cell to the cell to the right.

(2) Use Shift + Tab to move back (left) a cell.

 

Tab_Between_Cells

 

TIP # 7 Use Enter and Shift + Enter to Move Up an Down Cells for Easy Data Entry

(1) Use the Enter key to move a cell down.

(2) Use Shift + Enter to move up a cell.

 

Up_Down_Between_Cells

TIP # 8 Use Ctrl + D to Copy Data from the Cell Above and Use Ctrl + R to Copy Data From Cell to the Left

(1) Use Ctrl + D to copy cell contents directly above your current cell.

(2) Use Ctrl + R to copy cell contents from the cell to the left of your current cell.

 

Copy_Cell_Contents_Above_Left

TIP # 9 Use Excel’s Auto Fill Functionality to Quickly Fill in Rows and Columns Of Data Quickly

(1) You can use the small square in the bottom right hand corner of the cell that you are currently on to auto fill the surrounding cells by dragging and dropping.  This will auto-fill your surrounding data with the best possible matches.  If you have a formula in the current cell for example, the formula will be copied to the cells that you drag to.

(2) To auto fill the contents of the cell in a column only as far as the data in the columns to the left or right are filled, you can double click the small square in the bottom right hand corner of the cell you are currently on.

Auto_Fill_Image

TIP # 10 Use Alt, H, V to Make Options Available for Pasting Your Copied Data

(1) Use Ctrl + C to copy the data that you would like to paste.

(2) Once you copied the data, select a cell where you would like to paste your data then type Alt, H, V.  You’ll notice that multiple options become available (paste formulas, formulas and formatting, regular paste, paste numbers, transpose etc…).  Here we will transpose the data by typing the letter T (meaning flip from the current direction to the opposite – horizontal to vertical).

(3) You’ll notice that the data has been copied and pasted but transposed to be in a vertical direction as opposed to horizontal.

Copy_Paste_Transpose

 

TIP # 11  Drag the Contents of a Cell or Multiple Cells to a New Location/Destination

(1) Select a range of cells or a cell that you would like to move.  Place your cursor near the edge of your selection and you’ll notice a 4 point cursor.

(2) Left click and drag the cell contents to the new cell(s) where you would like the data displayed.

Drag_Drop_Data_Contents

 

TIP # 13 Use Ctrl + (+) to Insert a new Row/Column or Ctrl + (-) to Remove a Row/Column

(1) Select a range of cells, a cell or an entire row/column where you would like to add a column or a row.  Once you’ve done this, you can type Ctrl + (+).  If you’ve only selected a range of cells or a cell, you will then be asked if you want to shift cells to the right or left or if you want to add a new row or a column.

(2) Select a range of cells, a cell or an entire row/column where you would like to remove a column or a row.  Once you’ve done this, you can type Ctrl + (-).  If you’ve only selected a range of cells or a cell, you will then be asked if you want to shift cells to the left or right or if you want to delete the entire column or row.

delete-insert-rows-columns

TIP # 14 Use the Excel Auto Sum Option to Quickly Apply Common Formulas to a Range

(1) Use ALT, H, U, S (S for sum – here there are multiple options) to quickly apply a formula to the cells that are above your current range of cells.

Quick_Formula

TIP # 15 Use the Ctrl + ` (accent key – beside the number 1 on your keyboard) to Display All Formulas in a Spreadsheets

(1) Type Ctrl + ` (accent key) to display all of the formulas being used on the current spreadsheet.

show_all_formulas

TIP # 16 How To Quickly Navigate and Select Your Data Set Using Arrow Keys, the Ctrl Key and the Shift Key

(1) From your current cell, if you type Ctrl + Right Arrow Key it will bring you to the last column of the data set that you are currently on.  If there is no data to the right (in the columns beside your current column), you will be brought to the last column of the spreadsheet. You can use Ctrl + Right + Shift to actually select the cells.  Note if you have a blank cell within your range before another cell that does contain data, your cursor will stop there as opposed to the last column cell of the spreadsheet.

(2) You can then use Ctrl + Down Arrow key to go to the last row of your data set.  If you are at the very last row of your data set, you will be brought to the end of the spreadsheet. You can use Ctrl + Shift + Down to select the cells.  Note if you have a blank cell within your range before another cell that contains data, your cursor will stop at this row as opposed to going to the last row of the workbook.

(3) Use Ctrl + Shift + Right then Ctrl + Shift + Down to select your entire data set – Tip #17 will have a quicker solution to doing this however.

Ctrl_Right_Down Select Data

TIP # 17 Use Ctrl + A to Select the Entire Data Set Around your Current Cell

(1) Use Ctrl + A to select your entire data set quickly.  Note that you must currently be on a cell that is within the data set that you are working with.

Ctrl_A_Select All

TIP # 18 Create Multiple Name Ranges at Once Using Ctrl + Shift + F3

(1) Select a range of data for which you would like to create a name range.  Once you’ve done this type Ctrl + Shift + F3.  This will bring up a window where you can select which part of your data set the name range will be created for.  In our case, we will select the top row option and this will create ranges for each of our columns in the data set.

(2) Once we’ve created the name ranges, we can use those name ranges as references for our formula.  Below you’ll see I used the COUNTA formula to count the total number of tips (=COUNTA(TIP)).

Create Name Ranges

TIP # 19 Use Ctrl + K to Create a Hyperlink to Another Spreadsheet or Document

(1) Select a cell or an Excel object where you would like to add a hyperlink.  Type Ctrl + K and the hyperlink settings window will appear.  In our case, we our going to link another sheet in our workbook.

 

Ctrl_K_Hyperlink

TIP # 20 Use the Freeze Panes Functionality in Excel to Keep Parts of Your Spreadsheet Fully Visible At all Times

(1) Under the view tab in the Excel Quick Access Ribbon, under the window section there is a freeze panes option.  From here you have the ability to freeze the top row of your spreadsheet, you can freeze anything to the left or above the current cell you are on, or you can highlight an entire column or row and freeze any column/row to the right (column) or below (row) what you’ve highlighted.  As a shortcut, you can use Alt, W, F.

 

freeze_panes

 

TIP # 21  Use Alt + Shift + Right to Group Data or Alt + Shift + Left to Ungroup Data

(1) Select the data that you would like to group together. Here we selected all of the records where the customer lived in the state of California.  Now type Alt + Shift + Right arrow and a window will prompt you to group by based on the column or based on the row.  We will select row. Now you can use the + and – symbols on the left to minimize or maximize the group.

(2) Next, you can now ungroup the data that you selected by typing Alt + Shift + Left arrow.

group_ungroup_data

TIP # 22 Split Out Text from the Cells in a Column into Multiple Columns Using Text to Column

(1) Select a column where you would like to split out the contents of the cells into multiple columns.  Once you’ve done this, type Alt, A, E.  Add or make sure that there is a blank column to the right of the column from which you will be splitting out the text.

(2) Once you’ve done this you’ll want to select the delimited option from the wizard window.  On the next screen, you can select the option that best suits how you want to split out the data.  For example, if you have a dash (-) between the text that you would like to split out, we will use the other option and type the dash.  We will then hit next and then finish.

(3) The text will now be split out and added to the new column that was created.  If you do not have blank cells to the right of the column that you are splitting out the text from, Excel will ask you if you would like to overwrite the information to the right.

Text-to-Columns

TIP # 23 Use Alt, A, M to Remove Duplicate Records in a Data Set

(1) With the remove duplicates functionality in Excel you have the ability to remove duplicate records for a data set by first selecting your data range, then typing Alt, A, M.  This will prompt you with a window that displays all of your fields/columns (using your header records) and will ask you which fields or columns need to match in order to remove the records.  In our case, we are going to say that if all fields/columns have the same values for each record, we will remove all but one of the records (row).

remove_duplicates_2

 

TIP # 24 Use Alt, H, FP (Format Painter) to Copy the Formatting of One Range to Another

(1) Select a range of data from which you would like to copy the formatting.  Once you’ve selected the range, type Alt, H, FP.  This will copy that range and now you can use the drag and drop functionality of the paint cursor to select another range which will then copy your formatting.

format_painter

TIP # 25 How to Hide Tabs in a Workbook

(1) Right click on the tab in your workbook that you would like to hide and click the hide option.

(2) To unhide tabs, right click on any available tab and click unhide.

Hide_Unhide_tabs

 

TIP # 26 Use Alt, W, VG to Remove Grid Lines from a Spreadsheet

(1) Select the spreadsheet from which you would like to remove the grid lines.  Type Alt, W, VG and this will remove all grid lines on the current spreadsheet.

Remove_Gridlines

 

TIP # 27 Use Ctrl + N to Open a New Workbook from your Current Spreadsheet

 

New_Workbook

 

TIP # 28 Use Ctrl + Tab to Navigate Between Workbooks (or Ctrl + Shift Tab to Go Back a Worksheet)

Navigate_between_workbooks

TIP # 29 Use Ctrl + S to Save Files, Ctrl + F12 to Save As and Ctrl + P to Print

Save_Print_Options

 

If you prefer to go through the 29 tips through a video tutorial, you can do so by checking out my YouTube Channel or by checking out the video below.

Please make sure to let me know in the comments which tip you enjoyed the most or which tip you feel should have made the list.

As always thanks for your support!

To download the spreadsheet, click the link below:

thumbnail_Link_to_Spreadsheet

 

 

Excel Shortcut: Copy Formulas and/or Formatting to Multiple Sheets at Once – How to Identify Highest Sales For Each Country

 

I was just going through some handy shortcuts in Excel and thought I’d toss this on my website for all of you.  You can select multiple sheets/tabs at once in a workbook and apply a formula/formatting to a cell and have it be applied to all sheets.  You can also check out the formula I used in this example.  I used the index formula nested with the max formula to identify the highest sales value by product category.  This formula translates over automatically to each country tab.  Have a look for yourself.

 

APPLY_TO_MULTIPLE_SHEETS

 

If you have any questions feel free to drop a line in the comments.