Overall though, it does the trick for most situations. However, you’re limited on the icons you can select, and you cannot easily extract the exact color from a heat map. Excel has some built-in formats that make it easy to quickly create a heat map or even an icon overlay. And if you want to get fancy, you can tell Excel to format cells based on a formula that involves other cells. As its name implies, this feature allows you to format cells based on criteria you specify (instead of static formatting where the cell always holds the same formatting).įor instance, you could tell Excel to format/highlight all cells in a named range that are above a certain value. 4: Conditional formattingĬonditional formatting is fun, and I hope Microsoft expands this functionality a bit in the future. It’s not Business Objects, but it’s not bad for a spreadsheet tool.
Now just drag and drop columns, rows, and values (metrics) to dynamically create your cross-tab analysis. For small jobs, I’ll just put the pivot table next to the named range for larger jobs, I’ll give the pivot table its own sheet. With your named range in place (are you getting the sense of how fundamental these are when working with Excel?), click the pivot table button and then tell Excel where you want it to go. Although not as fully functional as a full-blown business intelligence tool, pivot tables in Excel do a respectable job of quickly cross-tabulating data and calculating counts, sums, and other aggregate metrics. Pivot tables are a quick and easy way to slice and dice data.
There’s also an extremely powerful Advanced Filtering functionality that allows you to filter your data set based on criteria you specify in another range. Excel’s okay with combining types, so you can quickly spot data errors just by looking at the different values in the filter drop-down. Once the range is in filter mode, it’s good to inspect the filter drop-downs to get a sense of the data in your data set. This is a fast and easy way to explore your data set and possibly highlight interesting rows or cells. 2: Sorting and filteringĪs soon as you have your named range in place, you can sort and filter with one click of the filter button. Put your primary key in the leftmost column and then use the VLOOKUP function anywhere in your workbook to find any value in your table. I typically think of these as lookup tables, so I usually use the “lkp” prefix when naming them. There are several ways to assign your custom name to the table, but I find it easiest to just click in the top left corner (where the cell reference is displayed) and start typing. No need to get fancy: column headings across the top row and then rows of data below, following the typical structure of any data table. In simple terms, a named range is a table of data that has a label for easy reference. Named ranges are a quick way to create a makeshift database in Excel. Here are five things about Excel that every data scientist should know. Microsoft Excel has been a secret weapon of mine for decades - it has been my ubiquitous data tool - and becoming a data scientist didn’t stop me one bit from using it. It’s hard to have a discussion about data science tools (and there’s a lot of that going on) without bringing up R, Python, and the rest of the darlings in the corral of data science favorites, but what about Excel? I’m sorry if Excel’s not sophisticated enough for your data science needs - or so you think. When is the last time you opened Microsoft Excel to do some data science? If it’s been a while, you’re missing out. He lists the go-to Excel skills data scientists should master. Microsoft Excel has been John Weathington's secret weapon for decades.
5 things every data scientist should know about Excel
0 kommentar(er)
