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

by Brad Edgar | Jul 2, 2014 | EXCEL BEGINNER, LEARN EXCEL SHORTCUTS

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 #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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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)).

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.

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.

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.

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.

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).

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.

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.

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.

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

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

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

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: