Being the 115th ranked general Excel blog on the internet sure has a nice ring to it. Well deserving of your e-mail subscription (located in the bottom right hand corner) if I was to be rather James Blunt about it. But what good is a rank if there are multiple categories to be considered?
If repetition is the key to mastery, repeating an activity the wrong way is a killer in disguise. A countless waste of hours dedicated to a mundane task. Fortunately, Excel is intended to avoid such things, and the distance between getting it done and being a master is much shorter than you may realize.
Undoubtedly you have called a function a formula and a formula a function; it's just the nature of the Excel-lingo beast. That stops now. I want you to see functions for what they truly are: computer programming. You may feel like you are not yet ready to call yourself a computer programmer - but you are. And I want to get you from here - to here - not through keyboard shortcuts but by taking the things that you are currently doing and giving you you the knowledge and confidence to know why you are doing them.
The founding fathers of Multiplan intended that spreadsheets would be for everyone: the rich and the poor, the data analyst and the data scientist, the sighted and the vision impaired. Often, things that we take for granted make a world of difference to others. Take the appropriate steps now to ensure that your work is up to accessibility standards.
The best part about having data is having CURRENT data. Obtaining up-to-date information shouldn't be a grind of opening workbook after workbook or constantly exporting database objects. It should be a quick Refresh All and then you're on to the next problem to solve. I saw you from across the Office through the watch window. You were sitting at the table with the perfectly cleansed data. I wanted to approach but just didn't know the correct queries to ask. Send me your ODC file.
If you find yourself becoming a mindless drone, constantly wondering when it's the weekend - then it's time to add to your arsenal. The WORKDAY function returns calendar work days quickly, but only when used correctly. Not every industry runs Monday through Friday though, so we'll cover WORKDAY.INTL for custom weekend schedules too. Put on your sunglasses, we're going in.
Analysts estimate anywhere from 50-80% of their time is dedicated to cleansing information (Source: New York Times). No longer will you spend hours trying to infer if "S" really means "Saturday" or "Sunday"; or trying to match "John Doe" with the employee's actual name "Johnathan Doe". The time you invest now in planning what is truly important and creating a set of sound variables will pay off infinitely in the end. Not only in making data entry easier for others, but also when it comes time to analyzing the things that matter: RESULTS.
With every passing second of every minute, of every hour - you are in a constant race against time. Trying to find time to exercise, to spend time with friends or family, or read the latest Excel TV article. Managing your schedule becomes even more important in the age of information. More data means more analysis, which means more reports. Either you beat the clock, or the clock beats you. But not everything is what it seems when it comes to time...
Not since the great territory battles of 7-Eleven Vs. Quiktrip, has there been such an unparalleled rivalry: "VLOOKUP or INDEX/MATCH?"A seemingly innocent question, when asked among Excel users, gives you a general perception of one another. There is one function's inability to turn left and the others reliance on multiple functions for use. One's perceived simplicity vs. the others perceived complexity. Are we so divided, though that we are unable to recognize the benefits found within each function? I'm unsure.. but I know neither function is willing to surrender their position without a fight.
Undoubtedly you have called a function a formula and a formula a function; it's just the nature of the Excel-lingo beast. SUM is the function everyone knows. SUMIF is the function most people discover eventually. But SUM(SUMIFS()) - an array formula that lets you sum across multiple criteria simultaneously - that's where things get interesting. I'm going to take you from Day 1 all the way to array formulas that will make your coworkers ask if you sold your soul to a spreadsheet demon. (You didn't. Probably.)