When you’re working in a column with names in “Last, First” format, for example, you historically had to either type everything out manually or create an often-complicated workaround.įlash Fill can automtically add data formatted the way you want without using formulas. Excel 2013 added Recommended PivotTables, making it even easier to create a table that displays the data you need.Įasily the best new feature in Excel 2013, Flash Fill solves one of the most frustrating problems of Excel: pulling needed pieces of information from a concatenated cell. PivotTables are essentially summary tables that let you count, average, sum, and perform other calculations according to the reference points you enter. If you’re doing a Vlookup to another sheet or table, for example, the IFERROR formula can render the field blank if the reference is not found. It lets you return a certain value (or a blank value) if the formula you’re trying to use returns an error.
IF formulas let you pull in just the data you need.
For example, you can identify students who scored 80 points or higher by having the cell report “Pass” if the score in column C is above 80, and “Fail” if it’s 79 or below. IF and IFERROR are the two most useful IF formulas in Excel. The IF formula lets you use conditional formulas that calculate one way when a certain thing is true, and another way when false. Don’t be afraid to play around in here-there are a surprising number of options.Įxcel 2013 includes Recommended Charts with layouts based on the type of data you’re working with.
Once the generic version of that chart is created, go to the Chart Tools menus to customize it. To create a chart, enter data into Excel with column headers, then select Insert > Chart > Chart Type. Excel 2013 even includes a Recommended Charts section with layouts based on the type of data you’re working with. Then enter the range of cells in the sheet or workbook from which you need to pull data, the column number for the data point you’re looking for, and either “True” (if you want the closest reference match) or “False” (if you require an exact match). Summon the Vlookup formula in the formula menu and enter the cell that contains your reference number. The Vlookup formula matches that ID to the corresponding ID in another sheet, so you can pull information like an item description, price, inventory levels, and other data points into your current workbook. You can use that as your reference point for Vlookups. Each product typically has a unique inventory number. Say you work with products in a retail store. Vlookup helps you find information in large data tables such as inventory lists. It helps you herd data that’s scattered across different sheets and workbooks and bring those sheets into a central location to create reports and summaries. Vlookup is the power tool every Excel user should know. You, obviously, are one of the latter-or are you? Check our list of 11 essential Excel skills to prove it-or discreetly pick up any you might have missed. There are two kinds of Microsoft Excel users in the world: Those who make neat little tables, and those who amaze their colleagues with sophisticated charts, data analysis, and seemingly magical formula and macro tricks.
0 kommentar(er)
