Excel Formulas- free tutorial Substring in Excel - Easy Excel Tutorial Mid | Left | Right | Substring between parentheses | Substring containing specific text | Flash Fill There's no SUBSTRING function in Excel. Use MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM and MAX in Excel to extract substrings. Mid To extract a substring, starting in the middle of a string, use the MID function in Excel. Explanation: the MID function starts at position 7 (O) and extracts 6 characters. Left To extract the leftmost characters from a string, use the LEFT function in Excel. To extract a substring (of any length) before the dash, add the FIND function. Explanation: the FIND function finds the position of the dash. Right To extract the rightmost characters from a string, use the RIGHT function in Excel. To extract a substring (of any length) after the dash, add LEN and FIND. Explanation: the LEN function returns the length of the string. Substring between parentheses To extract a substring between parentheses (or braces, brackets, slashes, etc.), use MID and FIND in Excel. 1.
Excel SUMIF function – formula examples to conditionally sum cells This tutorial explains the Excel SUMIF function in plain English and provides a numbers of SUMIF formula examples for numbers, text, dates and wildcards. If you are faced with the task that requires conditional sum in Excel, the SUMIF function is what you need. This tutorial will briefly explain the function's syntax and general usage, and then you will extend the new knowledge in practice with a number of SUMIF formula examples. A good thing is that the SUMIF function is identical in all Excel versions, from 2013 to 2003. Another great news is that once you've invested some time in learning SUMIF, it will take you very little effort to get the insight of other "IF" functions such as SUMIFS, COUNTIF, SOUNIFS, AVERAGEIF etc. SUMIF in Excel - syntax and usage The SUMIF function, also known as Excel conditional sum, is used to add cells based on a certain condition, or criteria. SUMIF(range, criteria, [sum_range]) 2. criteria - the condition that must be met. Note. Note. Note. Note. Note. 1.
23 things you should know about Excel pivot tables | Exceljet Many Excel experts believe that pivot tables are the single most powerful tool in Excel. According to Bill Jelen (a.k.a. Mr. Excel) "No other tool in Excel gives you the flexibility and analytical power of a pivot table". I agree completely. 1. Many people have the idea that building a pivot table is complicated and time-consuming, but it's simply not true. Select any cell in the source data On the Insert tab of the ribbon, click the PivotTable button In the Create PivotTable dialog box, check the data and click OK Drag a "label" field into the Row Labels area (e.g. customer) Drag a numeric field into the Values area (e.g. sales) Video: How to quickly create a pivot table Select any cell in the data to start. A very simple pivot table in about 30 seconds 2. To minimize problems down the road, always use good quality source data, organized in a tabular layout. Video: How to quickly fill in missing data Perfect data for a pivot table! 3. 300 first names means we have 300 employees. 4. 5. 6.
Excel COUNTIFS and COUNTIF with multiple criteria – examples of usage The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more. Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria. The difference is that the COUNTIF function is intended for counting cells based on a single condition in one range, while COUNTIFS allows using several criteria and ranges. In practice, however, you can use Excel COUNTIF with multiple criteria as well. Excel COUNTIFS function - syntax and usage As I've already mentioned, the Excel COUNTIFS function is purposed for counting cells across multiple ranges based on one or several conditions. COUNTIFS syntax The syntax of the COUNTIFS function is as follows: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) Example 1. Tip.
The Two Online Security Steps You Should Stop Putting Off - NYTimes.com My favorite password manager is LastPass — which our friends at The Wirecutter, a New York Times company, also recommend — because it works with every browser, it works with most mobile phones, and its interface is relatively simple. Most password managers generally work the same way: You set up one master password for the manager itself, and from then on you use its browser plugin (or mobile app) to create and manage every other password you need. Every account gets a unique password, meaning that if one account is compromised (like in a mass data breach), your other accounts will remain safe. If you’re still not convinced about the paramount importance of strong password management, here’s some further reading. Two-factor authentication goes a step further by requiring a username, password and access to your mobile phone to log in to an account. And, while we’re on the subject, here are more ways to protect your identity online. Have a great week! —Tim Best of Smarter Living
Excel Formulas: Defining and Creating a Formula In this lesson, we introduce you to basic rules for creating formulas and using functions. We feel one of the best ways to learn is through practice, so we provide several examples and explain them in detail. The topics we will cover include: rows and columnsexample math function: SUM()operatorsoperator precedenceexample financial function: PMT(), loan paymentusing a “string” function (“string” is shorthand for “string of text”) inside a formula and nesting functions Formulas are a mixture of “functions,” “operators,” and “operands.” Rows and Columns To understand how to write formulas and functions, you need to know about rows and columns. Rows run horizontally and columns run vertically. Columns are labeled by letters; rows by numbers. Example: Function Sum() Now let’s demonstrate how to use a function. You use functions by typing them directly in or using the function wizard. The wizard tells you what arguments you need to provide for each function. #NAME? Calculation Operators
How to Install and Move Android Apps to the SD Card If you have an Android phone or tablet with a small amount of storage, you probably keep uninstalling apps to make room for other ones. But there is a way to expand the storage of an Android device if it has an SD card slot. By default, Android apps install to your phone’s internal storage, which can be quite small. If you have an SD card, you can set it as the default install location for some apps–thus freeing up space for more apps than you would have otherwise been able to install. You can also move almost any currently installed app to the SD card. There are a few different ways to do this, and which you use depends on your version of Android and which apps you want to move. Before we start, we should note: running an app off your SD card will almost undoubtedly be slower than running it off the internal storage, so only use this if you absolutely have to–and if you can, try to use it for apps that don’t require a lot of speed to run well. Insert the SD card in your device.
Apply data validation to cells - Excel You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Check out this video by Doug from Office as he gives a quick data validation overview. Download our examples You can download an example workbook with all of the data validation examples you'll find in this article. Download Excel data validation examples Add data validation to a cell or a range Note: The first three steps in this section are for adding any type of data validation. Select one or more cells to validate. The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically. Notes: After you create your drop-down list, make sure it works the way you want. Adding other types of data validation The following table lists other types of data validation and shows you ways to add it to your worksheets. Examples of formulas in data validation
How to get a personal VPN and why you need one now — Quartz Elon Musk is the most wholesome visionary our era has produced. He is a benign idealist; a guy with his eyes on a horizon beyond money. Money? Musk doesn’t care about that. He hopes only to elevate our minds, our bodies, and our roads to other planes. Businesses, says the man who has founded so many of them, don’t really exist. Musk comes across as a tech monk who sees money as a means only to a good end. UBI is a policy gift that Musk and so many others in the C-suites of Silicon Valley offer us as part of their vision of a sustainable economic future. UBI is just bedtime story that helps the super-wealthy sleep. Instead, let’s believe that he, and the rest of Silicon Valley’s elite, are ultimately acting in the public interest. But let’s not let them all off so easily when it comes to their determined and growing support for UBI. Here’s the shameful secret not uttered in our favorite futurists’ TED-style presentations. The Notorious UBI UBI alternatives The reality of wealth realtors
Create and manage drop-down lists - Excel You can make a worksheet more efficient by providing drop-down lists. Someone using your worksheet clicks an arrow, and then clicks an entry in the list. Take this course to learn more about how best to use and manage them. Drop-down lists (1:01) Data entry is quicker and more accurate when you use a drop-down list to limit the entries people can make in a cell. Drop-down list settings (4:37) You can use a comma-delimited list, a cell range, or a named range to define the options in a drop-down list. Input and error messages (2:47) To help people decide what drop-down list option to select, and even to let them know that a cell contains a drop-down list, you can create a message that appears when they select a cell. Manage drop-down lists (6:20) To prevent changes to your drop-down list data, you can hide the columns, rows, or the entire worksheet that contains the data. Course summary A brief reminder of the key points in this course.