Learn Excel from Dennis Taylor, who has taught over 5,000 Excel seminars, classes, and webinars, and regularly provides Excel training across the US. Get more done in Excel 2016. Excel expert Dennis Taylor helps Excel users take their spreadsheet skills to the next level with this collection of tips and tricks. This is a quick tutorial on how to load Excel files from a SharePoint page. SharePoint is a nice landing place for your data because it can be connected to the. Excel Tips Tricks' title='Excel Tips Tricks' />Excel Advanced Tips and Tricks to Save Time and Get Known by Peers as an Excel Ninja. Have you been looking for Excel Advanced Tips and Tricks to make you faster and save your timeKnowing Excel Advanced Tips and Tricks could save you a lot of time and improve your productivity. A bonus you look like a ninja in front of your peers Get more from Office with these timesaving Microsoft Excel tipsstraight from the experts. Microsoft Excel Formulas. Learn Microsoft Excel Formulas Free With Ozgrid. Free Excel Formulae Training. Microsoft Excel is an amazing piece of software, and even regular users might not be getting as much out of it as they can. Improve your Excel efficiency. Excel Tips Tricks' title='Excel Tips Tricks' />Look no further in this article you will see 1. Excel to save time and get known by your peers as an Excel ninja. So, why wait Lets get started Keyboard Shortcuts. You will save tons of time and make moves like a ninja once you master the keyboard shortcuts. Here are some of my favorites Ctrl A will select all of the dataCtrl C will copy the selected data or objectCtrl V will paste the copied data or objectCtrl End will take you to the last cell of your dataCtrl Home will take you to the first cell of your dataCtrl Up Arrow will take you to the first cell of your active columnCtrl Down Arrow will take you to the last cell of your active columnShift Space will select rows of your active cellsCtrl Space will select columns of your active cellsShift Space will select rows of your active cellsvCtrl minus sign will give you Delete options. For a complete list of keyboard shortcuts, visit http bit. Qlj. QSp. 2 Copy Formula Down. To quickly copy a formula down Hover to the bottom right corner of the cell with the formula you will notice that the cursor has turned to a thick black plus signDouble click the plus sign. Copying Values or Formulas. When tip 2 doesnt work because theres a blank cell between your starting cell and the end cell of your range or you dont want to drag a formula down to 1. Go to the first cell that you would like to copy or fill in other cells. In the name box, type in the address of the last cell of the range, where you want to fill data or formula and hit Shift EnterPress F2 to edit the formula in your first cell. Then hit Ctr Enter You can see the name box highlighted in the red box. You will find this box right next to the f. Formula Bar. 4 Total a Column or a Row. To quickly total a column or a row, in the last cell, hit Alt equal to sign. Delete Duplicate Rows. To delete rows with duplicated data, follow these steps Select the range of the data that you want de duplicated usually, Ctrl A worksClick on the Data menu option from the menu ribbon. Click on the Remove Duplicates button. Choose whether your range has a header row. SUMIFS-excel-tips-and-tricks.png' alt='Excel Tips Tricks' title='Excel Tips Tricks' />Hit OK6 Add Leading Zeros. You often find the need to add leading zeros to a number and most likely the result value is a text format. For example, you may want to show the number 7. If your number is in A1 and you want to convert that to text with leading zeros with a maximum length of 1. B1 TEXTA1,REPT0,1. Repeat Header for Printing. While printing a multi page sheet, it is useful to repeat the header rows on every page. To do so,Click on Page Layout menu option on the riboon. Click on Print Titles button. Select the rows you want to repeat at the top in Rows to Repeat at Top box. Create Named Ranges. For sheets with large number of rows, it is useful to give names to ranges so that you can refer to these names in your formulas without clicking and selecting long ranges. Facebook Hacker Pro V7. To quickly give names to your ranges Click on the Formulas menu option on the ribbon. Click on the Create from Selection button. Excel Tips Tricks' title='Excel Tips Tricks' />Select the ranges to give names from usually Top Row works well as this row is the header row. Copy Values. To speed up copying only values and not the formulas, use this keyboard shortcut sequence Ctrl A to select the whole range of data or select your range with the help of the mouseCtrl C to copy the datain your destination, press Alt E, then S, then V, and then EnterThe whole sequence is Ctrl A, Ctrl C, Alt E, S, V, Enter. After you practice this a couple of times, when you do this in front of your peers, without a doubt they will be in awe of your new ninja abilities. Import a Table from the Web. Often you need to import data from the internet and you wish for a better way of doing so. Well, here is one way. Lets say you want to import the list of all time leaders of home run hitters in baseball from baseball almanac. Follow these steps to enjoy seeing this data in an Excel file Click on Data menu option from the ribbon. Click on From Web. In the browser window, enter the URL http www. Hit the Go button on the browser window. Scroll down to the table of home runs. Click on Click to select this table check box. Click on Import. Click on OK Only a ninja can import raw tables from the web to the Excel files Delete Current RowsTo quickly delete current rows, follow these two steps. Select the rows by Shift SpaceDelete the selected rows by Ctrl minus sign1. Delete Current ColumnsTo quickly delete current columns, follow these two steps. Select the columns by Ctrl SpaceDelete the selected columns by Ctrl minus sign1. Quick Aggregation. Quick Tool provide aggregate statistics, such as Average, Count, Numerical Count, Max, Min, and Sum of the data from a selected range without entering any formula. To show these statistics in the bottom toolbar, right click on the toolbar and choose the desired statistic. Finding a Related Value. Often you have to translate or cross walk a value, say a state code, to it a related value, in this case the fully spelled state name. You can write multiple, nested IF, but a ninja way is to use a VLOOKUP formula. The syntax of VLOOKUP formula is VLOOKUPlookupvalue,tablearray,colindexnum,rangelookupIn the above example, our lookup value is the state code and table array is a table with the state codes and their descriptions. The column index is the column that we want to return after matching a code. The range lookup is an argument to search for exact matches 1 or TRUE or approximate matches 0 or FALSELets say, your customer IDs in Column A, their mailing state code in Column B, and in Column C you want to see the state spelled out. To do so, follow these steps Create a table of codes and their fully spelled values in Column E and FIn C2, enter this formula  VLOOKUPB2,E3 F6,2,0Copy the formula down. Heres how it looks 1. Converting Numbers to Ranges. Did you ever find yourself with the need to convert a number column to discrete ranges for easier summaries or graphsDid you wish there was a better way Well, there is the VLOOKUP formula. You saw the syntax The syntax of VLOOKUP formula is VLOOKUPlookupvalue,tablearray,colindexnum,rangelookup To convert numbers to ranges, you use the rangelookup argument with a value of TRUE, which will tell Excel to make an approximate match to the lookup value. We can use this to our advantage. Lets say, you have some measure, such as, population, revenue, sales, of units, etc, in Column A. You want to convert these measures to certain ranges. To do so, follow these steps Create a table of measures and their discrete ranges in Column F and GIn C2, enter this formula VLOOKUPA2,F3 G8,2,1Copy the formula down. Heres how it looks 1. Text to Columns. Did you have a lots of data in a text document and you wish you could easily copy that to Excel Lets say you have a Word document with some bullet points, like this Vendor A 5,0. Vendor X 9,9. 00. Vendor D 1. 0,0. Vendor Z 3,5. To copy and keep the desired formatting for easier manipulation, follow these steps Copy the data from your Word document.