This Excel for beginners guide teaches you everything you need to know about Excel spreadsheets and formulas to perform financial analysis. Watch the Video and learn everything a beginner needs to know from what is Excel, to why do we use, and what are the most important keyboard shortcuts, functions, and formulas. 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.

• Excel Tips For Beginners Pdf

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. 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 # 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 # 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 + (-).

Excel Tips For Beginners Pdf

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

If you like this video, here's my entire playlist of Excel tutorials:Learn many of the intermediate skills, tips, and tricks that you'll need in order to be able to use Excel effectively. This is the second of several video tutorials on Excel.

Please consider watching my other video: The Beginner's Guide to Excel. In this intermediate video you'll learn some advanced formatting tools such as conditional formatting, how to save time by using the Autofill Handle, the basics of formulas and functions, sorting and filtering, and a few random useful tips such as Freeze Panes. Find my whole Excel playlist here:.Consider supporting Technology for Teachers and Students on Patreon.:Click below for more information about some of the movies mentioned in this tutorial:Dirty Rotten Scoundrels:The Princess Bride:Ladyhawke:Stardust:Young Sherlock Holmes:Mystery Men.