#even more commas slashes or parentheses | Explore Tumblr Posts and Blogs

amirbenbouza · 5 years

Text

Haskell Notes [Scratchpad]

[Personal Notes]

Functional Haskell is a functional language. If you have an imperative language background, you'll have to learn a lot of new things. Hopefully many of these new concepts will help you to program even in imperative languages.

Smart Static Typing Instead of being in your way like in C, C++ or Java, the type system is here to help you.

Purity Generally your functions won't modify anything in the outside world. This means, it can't modify the value of a variable, can't get user input, can't write on the screen, can't launch a missile. On the other hand, parallelism will be very easy to achieve. Haskell makes it clear where effects occur and where you are pure. Also, it will be far easier to reason about your program. Most bugs will be prevented in the pure parts of your program. Furthermore pure functions follow a fundamental law in Haskell: > Applying a function with the same parameters always returns the same value.

Laziness Laziness by default is a very uncommon language design. By default, Haskell evaluates something only when it is needed. In consequence, it provides a very elegant way to manipulate infinite structures for example.

Indentation ..is important in Haskell. Like in Python, a bad indentation could break your code!

Remark: Recursion is generally perceived as slow in imperative languages. But it is generally not the case in functional programming. Most of the time Haskell will handle recursive functions efficiently.

Introduction

Arithmetic: x^y -> for Integrals x**y -> for any type of number (float or double)

sum[] = 0 sum (n:ns) = n + sum ns #recursive function for summing numbers. Sum of empty list is zero.

:type functionName # You get the type of a function

- You cannot add or multiply numbers of vars of different types. You can specify the type using the :: operator. For example, double x :: int = x * 2 is a function that only that takes integers

- Scripts are saved with .hs extension. To run a script type: #ghci script.hs. If you load a script by simply invoking its name, its functions are in the scope, but if you modify the script while working in ghci, you need to :reload.

- White space and identation matter in Haskell. Either write with the same level of identation or write everything on one line. Use spaces and not tabs in Haskell for identation. Tabs will generate errors. If necessary convert them to 8 spaces.

- Comments are -- and extend to the end of the line. These are single line comments. Multi-line comments are {- -} and basically encapsulate multiple lines.

Lists Lists in Haskell are basically linked-lists. Each element contains the value of the element as well as a pointer to the next element in the list. Empty lists are represented by []. The operator Cons : takes two arguments on left and right and makes a list out of them.

Syntax: let mylist = 1 : ( 2: (3: [] ) ) is equal to let mylist = [1,2,3] 0:mylist [0,1,2,3] mylist:4 [1,2,3,4]

Generators: [1..4] = [1,2,3,4] [1,3..10] = [1,3,5,7,9] [2,3,5,7,11..100] => ERROR! I am not so smart! [ [10,9..1] = [10,9,8,7,6,5,4,3,2,1]

Mapping: Instead of using indexing on a list, we use the map function. map functionName listName newlist = map double mylist # map returns a new list not modifying the old one. newlist = map (\x -> x * 2) [1,2,3]

Generating Lists: let positiveInts = [1..] let firstTenPositive = [1..10] let firstTenEven = [2,4..20] let alphabet = ['a'..'z']

List comprehension (a la Python) let columns = ['a'..'f'] let rows = [1..6] let coords = [(column, row) | column <- columns, row <- rows]

List Functions: head [1,2,3] = 1 tail [1,2,3] = [2,3] take 2 [7,8,9] = [7,8] drop 2 [7,8,9] = [9] sum [2,3,4] = 9 product [2,3,4] = 24 zip [1,2,3] "abc" = [(1,'a'),(2,'b'),(3,'c')] unzip [(1,'a'),(2,'b'),(3,'c')] = ([1,2,3],"abc") filter (/= 2) [1,2,3] = [1,3] last[1,2,3] = 3 init[1,2,3] = [1,2] --removes the last element from a list : The Cons.truct operator, basically construcsts a list from two lists [1,2,3]:[4,5,6] or 1:[4,5,6].

Strings: Lists of chars. 'H' : 'ello' ~ 'Hello'. Anything that applies to strings applies to lists. length("Hello" ++ "World")

Conditional:

if expr==True then expr1; expr2; else expr1; expr2;

Note that the else is not optional.

Parentheses: Use $ to get rid of parentheses. Example: print $ "Hello World"

Variables

Functions functionaName :: ReturnType a => Arg1 -> Arg2 [Currying]

- Function: functionName arg1 arg2 ... argN = expr1;expr2;

- Function application has higher precedence than all other operations. Sometimes parentheses are needed to clear up confusion. f(x) in mathematics is f x in Haskell. f(x,y) is f x y in Haskell. f(g(x)) is f (g x), notice the difference. Parentheses are needed otherwise it would be interpreted as f(g,x) instead of f(g(x)).

- Function names: Begins with lower case, followed by n+ upper or lower case chars or numbers as well as `. If you see a function argument end with s it means the argument is a list (by convention), for example, double xs = map (*)

- Lambda functions: double xs = map (\x -> x * 2) xs # Here the argument is a list xs # We use the map function to apply a lambda / anonymous function (\x -> expr) to list xs

- In Haskell, a function is a mapping of a type or more (0..n) to another type or set of types. Think of it as a set theory mapping between two groups. T1 -> T2 means a function that takes arguments of Type 1 to return arguments of Type 2.

- When you type :type double x = x * 2, you will get double :: Double -> Double, meanining double :: T1 -> T2, the :: operator means of type.

-- By default: f g h x ⇔ (((f g) h) x)

-- the $ replace parenthesis from the $ -- to the end of the expression f g $ h x ⇔ f g (h x) ⇔ (f g) (h x) f $ g h x ⇔ f (g h x) ⇔ f ((g h) x) f $ g $ h x ⇔ f (g (h x)) -- (.) the composition function (f . g) x ⇔ f (g x) (f . g . h) x ⇔ f (g (h x))

Useful Function Notation: x :: Int ⇔ x is of type Int x :: a ⇔ x can be of any type x :: Num a => a ⇔ x can be any type a such that a belongs to Num type class f :: a -> b ⇔ f is a function from a to b f :: a -> b -> c ⇔ f is a function from a to (b→c) f :: (a -> b) -> c ⇔ f is a function from (a→b) to c

Polymorphic functions: Some functions are polymorphic such as length. Their type signatures include a "type variable", usually a lower case var(s). Think of it as Generic functions in other languages.

Curried / Partial application: Putting + in parentheses turns it into a curried function, which means (+) 1 2 3 means (1) (+ 2 3) means (+ 1 5) means 6. [TBC]

Types and Classes

Basic Types: Bool: Either True or False Char: Any char in the unicode system, as well as \n and \t. String: Sequence of chars. Strings must be enclosed in double quotes. Int (fixed-precision integers): -2^63 to 2^63 -1. Outside this range is unexpected results. 2^ 63 + 1 :: Int forces the expression to be of type Int, hence you will get a negative number. Int is unsigned. Integer – arbitrary-precision integers. Basically it fits (like BigNum) any number as long as your memory fits that number. Int has hardware support in most computers and is therefore faster (fixed-precision is supported by hardware). Arbitrary precision like Integer is in Software and therefore slower.

Float:Single-precision, depending on the compiler. Try sqrt 2 :: Float. Double: double-precision of float. Try sqrt 2 :: Double.

List: Same-types enclosed in brackets, seperated by comma, for example [1,2,3]. Length[T] can be used to obtain the length of a list. Rremember a list contains elements of the same type. Use :type var to get the type of a list. A list can have a non-finite length, and Haskell deals well with inifinite length lists (lazy evaluation.). [t] means a list when asking :type for types.

Tuples: Surrounded by parentheses, multiple types allowed, seperated by a comman. A type of size 1 is not allowed. Arity of a tuple is the number of its components. There are no restrictions on the different types of a tuple, for example tuple of tuples, tuples of bool and a list, etc. Tuples have a finite arity.

Classes: Types area collection of related values. Classses area a collection of Types, that have certain overloaded operations called methods. Some built-in classes, which we inherit from, and their methods, which can be overloaded:

Eq: include == (equal) and /= (not equal) (notice the sign is forward slash not backward). These two operators are overloaded for basic types, for example you can use them on Bool, String, and any other type. False == True -> False. "Amir" /= "Amir" -> False. All basic types are instances of the class Eq, as are list and tuples.

Ord: (<), (<=), (>), (>=), min, max. All basic types are instances of the class Ord, as are are lists and tuples. Lists and tuples are ordered lexicographically, in the same way words are ordered in a dictionary (compare first, if equal, move to second, if not, stop, etc).

Show: Converts any basic type into a string. show [1,2,3] -> "[1,2,3]".

Read: Opposite of show, takes a string and converts it to a basic type. Syntax is read "String" :: TypeToConvertTo. Sometimes the Type can be inferred by GHCi so no type information is necessary.

In other words, the basic types have / are "multiple inheritance" from multiple "classes" to gain access to their generic functions. Think of Clojure traits being inherited by every type in Haskell.

Examples: read "[1,2,3]" :: [Int] read "5.0" :: Double read "4" :: Int Prelude> x = read "[1,2,3]" :: [Int] Prelude> :type x x :: [Int]

Num: All typse whose value is numeric. (+) (-) (*) (negate) (abs) (signum). All arguments to a function that are negative must be surrounded by parentheses. Division is a special case handled two ways one for Integers one for Floats and Doubles.

Integral: Types that are part of the Num class but also support integer addititon and integer remainder functions. x `div` y x `mod` y

Fractional: Types that part of the Num class but also are non-integral meaning they support fractional division and fractional reciprocation: 8.0 / 4.0 -> 2.0 recip 2 = 1/2 -> 0.5 recip 4 = 1/4 -> 0.25

Conditional: - All If statements must have an Else part. - Haskelll provides a better way than If functions, namely guarded equations. abs n | n >0 = n | otherwise = -n

- "otherwise" is not necessary. It's definded as True by Prelude. Basically Haskell evaluates statements from first to last, if the nth expression is true, it stops. This is a bit similar to mathematical definitions of functions - "|" is read as "such as"

Basically nameOfFunction Parameter | Condition1 is True = Result

Wildcard pattern: It's a bit difficult to explain but sometimes you wanna simplify multiple expressions into one. For instance the && (logical AND) is only true if a and b are true a && a = True _ && _ = False. -- Assuming a is a Boolean value.

Signum: Returns 1 if the value is positive, -1 if value is negative, 0 if value is zero.

Lists and List Comprehension Recursive Functions

Modules: Haskell libraries are distributed in modules. To import a module simply add import module.submodule to the top of the file.

If only specific function needed: import module.submodule (function_name). If everything EXCEPT that function import module.submodule hiding (function_name). If we wish to import things to reference them by the module name, try import module.submodulbe as M M.funct arg1 Programs: Simply add a main function and save under .hs. double x = x *2 main = print(double 2)

Lambda expressions: \x -> x * 2 -- The \ means lambda in Greek letters -- How to use them? (\x -> x * 2) 2 map (\x -> x * 2) [1,2,3,4]

Operators: Functions written between their arguments are called operators. A normal function can be converted to an operator by enclosing it in single backquotes. The reverse is true: take an operator (+), and put it infix in front of its arguments (+) 4 5. add x y = x + y; 5 `add` 5 = 10.

List comprehension [x | x <- [1..10 ]]

Let

Guards

Guards are indicated by pipes that follow a function's name and its parameters. Usually, they're indented a bit to the right and lined up. A guard is basically a boolean expression. If it evaluates to True, then the corresponding function body is used. If it evaluates to False, checking drops through to the next guard and so on.

bmiTell :: (RealFloat a) => a -> String bmiTell bmi | bmi <= 18.5 = "You're underweight, you emo, you!" | bmi <= 25.0 = "You're supposedly normal. Pffft, I bet you're ugly!" | bmi <= 30.0 = "You're fat! Lose some weight, fatty!" | otherwise = "You're a whale, congratulations!"

Another version: bmiTell :: (RealFloat a) => a -> a -> String bmiTell weight height | bmi <= skinny = "You're underweight, you emo, you!" | bmi <= normal = "You're supposedly normal. Pffft, I bet you're ugly!" | bmi <= fat = "You're fat! Lose some weight, fatty!" | otherwise = "You're a whale, congratulations!" where bmi = weight / height ^ 2 skinny = 18.5 normal = 25.0 fat = 30.0 The names we define in the where section of a function are only visible to that function, so we don't have to worry about them polluting the namespace of other functions. Notice that all the names are aligned at a single column. If we don't align them nice and proper, Haskell gets confused because then it doesn't know they're all part of the same block.

Pattern matching When defining a function you could define it for certain arguments abd pattern match its argument list.

Example lucky :: (Integral a) => a -> String lucky 7 = "LUCKY NUMBER SEVEN!" lucky x = "Sorry, you're out of luck, pal!" You pattern match the argument values. Yes you could do it with if statements but this is easier for the eye. Basically they are evaluated from the top to the bottom, whichever statement clicks then it stops evaluation. If it reaches x then none clicked and the x statement is triggered. When making patterns, we should always include a catch-all pattern so that our program doesn't crash if we get some unexpected input.w

Another Example: sayMe :: (Integral a) => a -> String sayMe 1 = "One!" sayMe 2 = "Two!" sayMe 3 = "Three!" sayMe 4 = "Four!" sayMe 5 = "Five!" sayMe x = "Not between 1 and 5"

Patterns and Guards are a bit similar. Guards allow you to run tests and more flexibility. Patterns are less flexible and depend on the call value of the function.

Classes Conditionals

Curried Functions: Basically all functions in Haskell take one parameter which returns a value and a function call. In other words (+ 6) 5

Partial Application:

Lambda functions anonymousFunction = (/x -> x + 1) = (/x)

Higher-Order Functions: Haskell functions can take functions as parameters and return functions as return values. A function that does either of those is called a higher order function. Functions that take other functions as parameters. Examples include map, filter, foldl.

Filter Filter takes a function of type a->True, and a list, and returns only the variables for which that function evaluates as true. For example: filter even [1..10] returns all even numbers in the sequence 1 to 10 inclusive.

Map Map takes a function and a list and returns a new list, which is basically that function, applied to every element of the first list. map (\x -> x*2) [1..20] -- notice the arrow direction

Fold (foldl, foldr) Takes an operator or a function and applies between the members of a list either from the left or the right. So foldl (+) 0 lst is (0 + 1 + foldl lst) (1 + 2 foldl lst) (3 + foldl lst) Takes two arguments, a function and a number. If L, it adds that number or element to the left, if right it adds it to the right, then it inserts the function between all the members of the list, then starts processing from right (foldr) or left (foldl). The difference between foldr and foldl is that some functions do not care if applied from left to right, or right to left (the plus sign for example), but other functions might care about whether it is applied from a certain direction (the minus sign)

fold (+) 0 [1,2,3] is equal to: 1. Imagine the list and write it down [1][2][3] 2. Leave a space between each member [1] [2] [3] 3. Add the element to the left or right side depending on the version [0] [1] [2] [3] 4. Replace the space with functions and apply the function from left to right or right to left depending on the direction [0]+[1]+[2]+[3] = 6

foldr (-) 0 [1,2,3] = 1,2,3,0, 1 2 3 0, 0 - 3 - 2 - 1 = -6 foldl (-) 0 [1,2,3] = 0,1,2,3, 0 1 2 3, 0 - 1 - 2 - 3 = -6 foldr (+) 0 [1,2,3] = 1,2,3,0, 1 2 3 0, 0 + 3 + 2 + 1 = 6 foldl (+) 0 [1,2,3] = 0,1,2,3, 0+1+2+3 = 6 {- Todo: - Classes - Interfaces - File I/O - Monads - Real World Haskell - Lambda Calculus - Fix Sublime Text REPL / Haskell / Haskell IDE -}

0 notes

lindyhunt · 5 years

Text

15 Excel Formulas, Keyboard Shortcuts & Tricks That'll Save You Lots of Time

For most marketers, trying to organize and analyze spreadsheets in Microsoft Excel can feel like walking into a brick wall over and over again. You're manually replicating columns and scribbling down long-form math on a scrap of paper, all while thinking to yourself, "There has to be a better way to do this."

Truth be told, there is -- you just don't know it yet.

Excel can be tricky that way. On the one hand, it's an exceptionally powerful tool for reporting and analyzing marketing data. On the other, without the proper training, it's easy to feel like it's working against you. For starters, there are more than a dozen critical formulas Excel can automatically run for you so you're not combing through hundreds of cells with a calculator on your desk.

How to Insert Formulas in Excel

You might wonder what the "Formulas" tab on the top navigation toolbar in Excel means. In more recent versions of Excel, this horizontal menu -- shown below -- allows you to automatically find and insert Excel formulas into specific cells of your spreadsheet.

The more you use various formulas in Excel, the easier it'll be to remember them and perform them manually. Nonetheless, the suite of icons above is a handy catalog of formulas you can browse and refer back to as you hone your spreadsheet skills.

Excel formulas are also called "functions." To insert one into your spreadsheet, highlight a cell in which you want to run a formula, then click the far-left icon, "Insert Function," to browse popular formulas and what they do. That browsing window will look like this:

Want a more sorted browsing experience? Use any of the icons we've highlighted (inside the long red rectangle in the first screenshot above) to find formulas related to a variety of common subjects -- such as finance, logic, and more. Once you've found the formula that suits your needs, click "Insert Function," as shown in the window above.

Now, let's do a deeper dive into some of the most crucial Excel formulas and how to perform each one in typical situations.

Excel Formulas

SUM

Percentage

Subtraction

Multiplication

Division

DATE

Array

COUNT

AVERAGE

SUMIF

TRIM

LEFT, MID, and RIGHT

VLOOKUP

RANDOMIZE

To help you use Excel more effectively (and save a ton of time), we've compiled a list of essential formulas, keyboard shortcuts, and other small tricks and functions you should know.

NOTE: The following formulas apply to Excel 2017. If you're using a slightly older version of Excel, the location of each feature mentioned below might be slightly different.

1. SUM

All Excel formulas begin with the equals sign, =, followed by a specific text tag denoting the formula you'd like Excel to perform.

The SUM formula in Excel is one of the most basic formulas you can enter into a spreadsheet, allowing you to find the sum (or total) of two or more values. To perform the SUM formula, enter the values you'd like to add together using the format, =SUM(value 1, value 2, etc).

The values you enter into the SUM formula can either be actual numbers or equal to the number in a specific cell of your spreadsheet.

To find the SUM of 30 and 80, for example, type the following formula into a cell of your spreadsheet: =SUM(30, 80). Press "Enter," and the cell will produce the total of both numbers: 110.

To find the SUM of the values in cells B2 and B11, for example, type the following formula into a cell of your spreadsheet: =SUM(B2, B11). Press "Enter," and the cell will produce the total of the numbers currently filled in cells B2 and B11. If there are no numbers in either cell, the formula will return 0.

Keep in mind you can also find the total value of a list of numbers in Excel. To find the SUM of the values in cells B2 through B11, type the following formula into a cell of your spreadsheet: =SUM(B2:B11). Note the colon between both cells, rather than a comma. See how this might look in an Excel spreadsheet for a content marketer, below:

2. IF

The IF formula in Excel is denoted =IF(logical_test, value_if_true, value_if_false). This allows you to enter a text value into the cell "if" something else in your spreadsheet is true or false. For example, =IF(D2="Gryffindor","10","0") would award 10 points to cell D2 if that cell contained the word "Gryffindor."

There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it.

We'll go back to Sprung's example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10's next to each Gryffindor student's name, we'll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.

The formula: IF(logical_test, value_if_true, value_if_false)

Logical_Test: The logical test is the "IF" part of the statement. In this case, the logic is D2="Gryffindor." Make sure your Logical_Test value is in quotation marks.

Value_if_True: If the value is true -- that is, if the student lives in Gryffindor -- this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number.

Value_if_False: If the value is false -- and the student does not live in Gryffindor -- we want the cell to show "0," for 0 points.

Formula in below example: =IF(D2="Gryffindor","10","0")

3. Percentage

To perform the percentage formula in Excel, enter the cells you're finding a percentage for in the format, =A1/B1. To convert the resulting decimal value to a percentage, highlight the cell, click the Home tab, and select "Percentage" from the numbers dropdown.

There isn't an Excel "formula" for percentages per se, but Excel makes it easy to convert the value of any cell into a percentage so you're not stuck calculating and reentering the numbers yourself.

The basic setting to convert a cell's value into a percentage is under Excel's Home tab. Select this tab, highlight the cell(s) you'd like to convert to a percentage, and click into the dropdown menu next to Conditional Formatting (this menu button might say "General" at first). Then, select "Percentage" from the list of options that appears. This will convert the value of each cell you've highlighted into a percentage. See this feature below.

Keep in mind if you're using other formulas, such as the division formula (denoted =A1/B1), to return new values, your values might show up as decimals by default. Simply highlight your cells before or after you perform this formula, and set these cells' format to "Percentage" from the Home tab -- as shown above.

4. Subtraction

To perform the subtraction formula in Excel, enter the cells you're subtracting in the format, =SUM(A1, -B1). This will subtract a cell using the SUM formula by adding a negative sign before the cell you're subtracting. For example, if A1 was 10 and B1 was 6, =SUM(A1, -B1) would perform 10 + -6, returning a value of 4.

Like percentages, subtracting doesn't have its own formula in Excel either, but that doesn't mean it can't be done. You can subtract any values (or those values inside cells) two different ways.

Using the =SUM formula. To subtract multiple values from one another, enter the cells you'd like to subtract in the format =SUM(A1, -B1), with a negative sign (denoted with a hyphen) before the cell whose value you're subtracting. Press enter to return the difference between both cells included in the parentheses. See how this looks in the screenshot above.

Using the format, =A1-B1. To subtract multiple values from one another, simply type an equals sign followed by your first value or cell, a hyphen, and the value or cell you're subtracting. Press Enter to return the difference between both values.

5. Multiplication

To perform the multiplication formula in Excel, enter the cells you're multiplying in the format, =A1*B1. This formula uses an asterisk to multiply cell A1 by cell B1. For example, if A1 was 10 and B1 was 6, =A1*B1 would return a value of 60.

You might think multiplying values in Excel has its own formula or uses the "x" character to denote multiplication between multiple values. Actually, it's as easy as an asterisk -- *.

To multiply two or more values in an Excel spreadsheet, highlight an empty cell. Then, enter the values or cells you want to multiply together in the format, =A1*B1*C1 ... etc. The asterisk will effectively multiply each value included in the formula.

Press Enter to return your desired product. See how this looks in the screenshot above.

6. Division

To perform the division formula in Excel, enter the cells you're dividing in the format, =A1/B1. This formula uses a forward slash, "/," to divide cell A1 by cell B1. For example, if A1 was 5 and B1 was 10, =A1/B1 would return a decimal value of 0.5.

Division in Excel is one of the simplest functions you can perform. To do so, highlight an empty cell, enter an equals sign, "=," and follow it up with the two (or more) values you'd like to divide with a forward slash, "/," in between. The result should be in the following format: =B2/A2, as shown in the screenshot below.

Hit Enter, and your desired quotient should appear in the cell you initially highlighted.

7. DATE

The Excel DATE formula is denoted =DATE(year, month, day). This formula will return a date that corresponds to the values entered in the parentheses -- even values referred from other cells. For example, if A1 was 2018, B1 was 7, and C1 was 11, =DATE(A1,B1,C1) would return 7/11/2018.

Creating dates in the cells of an Excel spreadsheet can be a fickle task every now and then. Luckily, there's a handy formula to make formatting your dates easy. There are two ways to use this formula:

Create dates from a series of cell values. To do this, highlight an empty cell, enter "=DATE," and in parentheses, enter the cells whose values create your desired date -- starting with the year, then the month number, then the day. The final format should look like this: =DATE(year, month, day). See how this looks in the screenshot below.

Automatically set today's date. To do this, highlight an empty cell and enter the following string of text: =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())). Pressing enter will return the current date you're working in your Excel spreadsheet.

In either usage of Excel's date formula, your returned date should be in the form of "mm/dd/yy" -- unless your Excel program is formatted differently.

8. Array

An array formula in Excel surrounds a simple formula in brace characters using the format, {=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)}. By pressing ctrl+shift+center, this will calculate and return value from multiple ranges, rather than just individual cells added to or multiplied by one another.

Calculating the sum, product, or quotient of individual cells is easy -- just use the =SUM formula and enter the cells, values, or range of cells you want to perform that arithmetic on. But what about multiple ranges? How do you find the combined value of a large group of cells?

Numerical arrays are a useful way to perform more than one formula at the same time in a single cell so you can see one final sum, difference, product, or quotient. If you're looking to find total sales revenue from several sold units, for example, the array formula in Excel is perfect for you. Here's how you'd do it:

To start using the array formula, type "=SUM," and in parentheses, enter the first of two (or three, or four) ranges of cells you'd like to multiply together. Here's what your progress might look like: =SUM(C2:C5

Next, add an asterisk after the last cell of the first range you included in your formula. This stands for multiplication. Following this asterisk, enter your second range of cells. You'll be multiplying this second range of cells by the first. Your progress in this formula should now look like this: =SUM(C2:C5*D2:D5)

Ready to press Enter? Not so fast ... Because this formula is so complicated, Excel reserves a different keyboard command for arrays. Once you've closed the parentheses on your array formula, press Ctrl+Shift+Enter. This will recognize your formula as an array, wrapping your formula in brace characters and successfully returning your product of both ranges combined.

In revenue calculations, this can cut down on your time and effort significantly. See the final formula in the screenshot above.

9. COUNT

The COUNT formula in Excel is denoted =COUNT(Start Cell:End Cell). This formula will return a value that is equal to the number of entries found within your desired range of cells. For example, if there are eight cells with entered values between A1 and A10, =COUNT(A1:A10) will return a value of 8.

The COUNT formula in Excel is particularly useful for large spreadsheets, wherein you want to see how many cells contain actual entries. Don't be fooled: This formula won't do any math on the values of the cells themselves. This formula is simply to find out how many cells in a selected range are occupied with something.

Using the formula in bold above, you can easily run a count of active cells in your spreadsheet. The result will look a little something like this:

10. AVERAGE

To perform the average formula in Excel, enter the values, cells, or range of cells of which you're calculating the average in the format, =AVERAGE(number1, number2, etc.) or =AVERAGE(Start Value:End Value). This will calculate the average of all the values or range of cells included in the parentheses.

Finding the average of a range of cells in Excel keeps you from having to find individual sums and then performing a separate division equation on your total. Using =AVERAGE as your initial text entry, you can let Excel do all the work for you.

For reference, the average of a group of numbers is equal to the sum of those numbers, divided by the number of items in that group.

11. SUMIF

The SUMIF formula in Excel is denoted =SUMIF(range, criteria, [sum range]). This will return the sum of the values within a desired range of cells that all meet one criterion. For example, =SUMIF(C3:C12,">70,000") would return the sum of values between cells C3 and C12 from only the cells that are greater than 70,000.

Let's say you want to determine the profit you generated from a list of leads who are associated with specific area codes, or calculate the sum of certain employees' salaries -- but only if they fall above a particular amount. Doing that manually sounds a bit time-consuming, to say the least.

With the SUMIF function, it doesn't have to be -- you can easily add up the sum of cells that meet certain criteria, like in the salary example above.

The formula: =SUMIF(range, criteria, [sum_range])

Range: The range that is being tested using your criteria.

Criteria: The criteria that determine which cells in Criteria_range1 will be added together

[Sum_range]: An optional range of cells you're going to add up in addition to the first Range entered. This field may be omitted.

In the example below, we wanted to calculate the sum of the salaries that were greater than $70,000. The SUMIF function added up the dollar amounts that exceeded that number in the cells C3 through C12, with the formula =SUMIF(C3:C12,">70,000").

12. TRIM

The TRIM formula in Excel is denoted =TRIM(text). This formula will remove any spaces entered before and after the text entered in the cell. For example, if A2 includes the name " Steve Peterson" with unwanted spaces before the first name, =TRIM(A2) would return "Steve Peterson" with no spaces in a new cell.

Email and file sharing are wonderful tools in today's workplace. That is, until one of your colleagues sends you a worksheet with some really funky spacing. Not only can those rogue spaces make it difficult to search for data, but they also affect the results when you try to add up columns of numbers.

Rather than painstakingly removing and adding spaces as needed, you can clean up any irregular spacing using the TRIM function, which is used to remove extra spaces from data (except for single spaces between words).

The formula: =TRIM(text).

Text: The text or cell from which you want to remove spaces.

Here's an example of how we used the TRIM function to remove extra spaces before a list of names. To do so, we entered =TRIM("A2") into the Formula Bar, and replicated this for each name below it in a new column next to the column with unwanted spaces.

Below are some other Excel formulas you might find useful as your data management needs grow.

13. LEFT, MID, and RIGHT

Let's say you have a line of text within a cell that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed: LEFT, MID, or RIGHT.

LEFT

Purpose: Used to extract the first X numbers or characters in a cell.

The formula: =LEFT(text, number_of_characters)

Text: The string that you wish to extract from.

Number_of_characters: The number of characters that you wish to extract starting from the left-most character.

In the example below, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. That allowed us to extract the first 4 characters of the code.

MID

Purpose: Used to extract characters or numbers in the middle based on position.

The formula: =MID(text, start_position, number_of_characters)

Text: The string that you wish to extract from.

Start_position: The position in the string that you want to begin extracting from. For example, the first position in the string is 1.

Number_of_characters: The number of characters that you wish to extract.

In this example, we entered =MID(A2,5,2) into cell B2, and copied it into B3:B6. That allowed us to extract the two numbers starting in the fifth position of the code.

RIGHT

Purpose: Used to extract the last X numbers or characters in a cell.

The formula: =RIGHT(text, number_of_characters)

Text: The string that you wish to extract from.

Number_of_characters: The number of characters that you want to extract starting from the right-most character.

For the sake of this example, we entered =RIGHT(A2,2) into cell B2, and copied it into B3:B6. That allowed us to extract the last two numbers of the code.

14. VLOOKUP

This one is an oldie, but a goodie -- and it's a bit more in depth than some of the other formulas we've listed here. But it's especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet.

My colleague, Rachel Sprung -- whose "How to Use Excel" tutorial is a must-read for anyone who wants to learn -- uses a list of names, email addresses, and companies as an example. If you have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other, but you want the names, email addresses, and company names of those people to appear in one place -- that's where VLOOKUP comes in.

Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.

The formula: VLOOKUP(lookup value, table array, column number, [range lookup])

Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung's example that follows, this means the first email address on the list, or cell 2 (C2).

Table Array: The range of columns on Sheet 2 you're going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you're trying to copy to Sheet 1. In our example, this is "Sheet2!A:B." "A" means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The "B" means Column B, which contains the information that's only available in Sheet 2 that you want to translate to Sheet 1.

Column Number: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the "House" column, the second one in our table array, making it column number 2.

Range Lookup: Use FALSE to ensure you pull in only exact value matches.

The formula with variables from Sprung's example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this example, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1. Here's how that would work:

15. RANDOMIZE

There's a great article that likens Excel's RANDOMIZE formula to shuffling a deck of cards. The entire deck is a column, and each card -- 52 in a deck -- is a row. "To shuffle the deck," writes Steve McDonnell, "you can compute a new column of data, populate each cell in the column with a random number, and sort the workbook based on the random number field."

In marketing, you might use this feature when you want to assign a random number to a list of contacts -- like if you wanted to experiment with a new email campaign and had to use blind criteria to select who would receive it. By assigning numbers to said contacts, you could apply the rule, “Any contact with a figure of 6 or above will be added to the new campaign.”

The formula: RAND()

Start with a single column of contacts. Then, in the column adjacent to it, type “RAND()” -- without the quotation marks -- starting with the top contact’s row.

For the example below: RANDBETWEEN(bottom,top)

RANDBETWEEN allows you to dictate the range of numbers that you want to be assigned. In the case of this example, I wanted to use one through 10.

bottom: The lowest number in the range.

top: The highest number in the range,

Formula in below example: =RANDBETWEEN(1,10)

Helpful stuff, right? Now for the icing on the cake: Once you've mastered the Excel formula you need, you'll want to replicate it for other cells without rewriting the formula. And luckily, there's an Excel function for that, too. Check it out below.

How to Insert Formula in Excel for Entire Column

To insert a formula in Excel for an entire column of your spreadsheet, enter the formula into the topmost cell of your desired column and press "Enter." Then, highlight and double-click the bottom-right corner of this cell to copy the formula into every cell below it in the column.

Sometimes, you might want to run the same formula across an entire row or column of your spreadsheet. Let's say, for example, you have a list of numbers in columns A and B of a spreadsheet and want to enter individual totals of each row into column C.

Obviously, it would be too tedious to adjust the values of the formula for each cell so you're finding the total of each row's respective numbers. Luckily, Excel allows you to automatically compete the column; all you have to do is enter the formula in the first row. Check out the following steps:

Type your formula into an empty cell and press "Enter" to run the formula.

Hover your cursor over the bottom-right corner of the cell containing the formula. You'll see a small, bold "+" symbol appear.

While you can double-click this symbol to automatically fill the entire column with your formula, you can also click and drag your cursor down manually to fill only a specific length of the column.Once you've reached the last cell in the column you'd like to enter your formula, release your mouse to copy the formula. Then, simply check each new value to ensure it corresponds to the correct cells.

Excel Keyboard Shortcuts

1. Quickly select rows, columns, or the whole spreadsheet.

Perhaps you're crunched for time. I mean, who isn't? No time, no problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.

Just want to select everything in a particular column of row? That's just as easy with these shortcuts:

For Mac:

Select Column = Command + Shift + Down/Up

Select Row = Command + Shift + Right/Left

For PC:

Select Column = Control + Shift + Down/Up

Select Row = Control + Shift + Right/Left

This shortcut is especially helpful when you're working with larger data sets, but only need to select a specific piece of it.

2. Quickly open, close, or create a workbook.

Need to open, close, or create a workbook on the fly? The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute's time.

For Mac:

Open = Command + O

Close = Command + W

Create New = Command + N

For PC:

Open = Control + O

Close = Control + F4

Create New = Control + N

3. Format numbers into currency.

Have raw data that you want to turn into currency? Whether it be salary figures, marketing budgets, or ticket sales for an event, the solution is simple. Just highlight the cells you wish to reformat, and select Control + Shift + $.

The numbers will automatically translate into dollar amounts -- complete with dollar signs, commas, and decimal points.

Note: This shortcut also works with percentages. If you want to label a column of numerical values as "percent" figures, replace "$" with "%".

4. Insert current date and time into a cell.

Whether you're logging social media posts, or keeping track of tasks you're checking off your to-do list, you might want to add a date and time stamp to your worksheet. Start by selecting the cell to which you want to add this information.

Then, depending on what you want to insert, do one of the following:

Insert current date = Control + ; (semi-colon)

Insert current time = Control + Shift + ; (semi-colon)

Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).

Other Excel Tricks

1. Customize the color of your tabs.

If you've got a ton of different sheets in one workbook -- which happens to the best of us -- make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month's marketing reports with red, and this month's with orange.

Simply right click a tab and select "Tab Color." A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.

2. Add a comment to a cell.

When you want to make a note or add a comment to a specific cell within a worksheet, simply right-click the cell you want to comment on, then click Insert Comment. Type your comment into the text box, and click outside the comment box to save it.

Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.

3. Copy and duplicate formatting.

If you've ever spent some time formatting a sheet to your liking, you probably agree that it's not exactly the most enjoyable activity. In fact, it's pretty tedious.

For that reason, it's likely that you don't want to repeat the process next time -- nor do you have to. Thanks to Excel's Format Painter, you can easily copy the formatting from one area of a worksheet to another.

Select what you'd like to replicate, then select the Format Painter option -- the paintbrush icon -- from the dashboard. The pointer will then display a paintbrush, prompting you to select the cell, text, or entire worksheet to which you want to apply that formatting, as shown below:

4. Identify duplicate values.

In many instances, duplicate values -- like duplicate content when managing SEO -- can be troublesome if gone uncorrected. In some cases, though, you simply need to be aware of it.

Whatever the situation may be, it's easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values

Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.

In the example above, we were looking to identify any duplicate salaries within the selected range, and formatted the duplicate cells in yellow.

In marketing, the use of Excel is pretty inevitable -- but with these tricks, it doesn't have to be so daunting. As they say, practice makes perfect. The more you use these formulas, shortcuts, and tricks, the more they'll become second nature.

To dig a little deeper, check out a few of our favorite resources for learning Excel. Want more Excel tips? Check out this post on how to create a pivot table with medians.

0 notes

readersforum · 5 years

Text

15 Excel Formulas, Keyboard Shortcuts & Tricks That'll Save You Lots of Time

New Post has been published on http://www.readersforum.tk/15-excel-formulas-keyboard-shortcuts-tricks-thatll-save-you-lots-of-time/

15 Excel Formulas, Keyboard Shortcuts & Tricks That'll Save You Lots of Time

For most marketers, trying to organize and analyze spreadsheets in Microsoft Excel can feel like walking into a brick wall over and over again. You’re manually replicating columns and scribbling down long-form math on a scrap of paper, all while thinking to yourself, “There has to be a better way to do this.”

Truth be told, there is — you just don’t know it yet.

Excel can be tricky that way. On the one hand, it’s an exceptionally powerful tool for reporting and analyzing marketing data. On the other, without the proper training, it’s easy to feel like it’s working against you. For starters, there are more than a dozen critical formulas Excel can automatically run for you so you’re not combing through hundreds of cells with a calculator on your desk.

How to Insert Formulas in Excel

You might wonder what the “Formulas” tab on the top navigation toolbar in Excel means. In more recent versions of Excel, this horizontal menu — shown below — allows you to automatically find and insert Excel formulas into specific cells of your spreadsheet.

The more you use various formulas in Excel, the easier it’ll be to remember them and perform them manually. Nonetheless, the suite of icons above is a handy catalog of formulas you can browse and refer back to as you hone your spreadsheet skills.

Excel formulas are also called “functions.” To insert one into your spreadsheet, highlight a cell in which you want to run a formula, then click the far-left icon, “Insert Function,” to browse popular formulas and what they do. That browsing window will look like this:

Want a more sorted browsing experience? Use any of the icons we’ve highlighted (inside the long red rectangle in the first screenshot above) to find formulas related to a variety of common subjects — such as finance, logic, and more. Once you’ve found the formula that suits your needs, click “Insert Function,” as shown in the window above.

Now, let’s do a deeper dive into some of the most crucial Excel formulas and how to perform each one in typical situations.

Excel Formulas

SUM

Percentage

Subtraction

Multiplication

Division

DATE

Array

COUNT

AVERAGE

SUMIF

TRIM

LEFT, MID, and RIGHT

VLOOKUP

RANDOMIZE

To help you use Excel more effectively (and save a ton of time), we’ve compiled a list of essential formulas, keyboard shortcuts, and other small tricks and functions you should know.

NOTE: The following formulas apply to Excel 2017. If you’re using a slightly older version of Excel, the location of each feature mentioned below might be slightly different.

1. SUM

All Excel formulas begin with the equals sign, =, followed by a specific text tag denoting the formula you’d like Excel to perform.

The SUM formula in Excel is one of the most basic formulas you can enter into a spreadsheet, allowing you to find the sum (or total) of two or more values. To perform the SUM formula, enter the values you’d like to add together using the format, =SUM(value 1, value 2, etc).

The values you enter into the SUM formula can either be actual numbers or equal to the number in a specific cell of your spreadsheet.

To find the SUM of 30 and 80, for example, type the following formula into a cell of your spreadsheet: =SUM(30, 80). Press “Enter,” and the cell will produce the total of both numbers: 110.

To find the SUM of the values in cells B2 and B11, for example, type the following formula into a cell of your spreadsheet: =SUM(B2, B11). Press “Enter,” and the cell will produce the total of the numbers currently filled in cells B2 and B11. If there are no numbers in either cell, the formula will return 0.

2. IF

The IF formula in Excel is denoted =IF(logical_test, value_if_true, value_if_false). This allows you to enter a text value into the cell “if” something else in your spreadsheet is true or false. For example, =IF(D2=”Gryffindor”,”10″,”0″) would award 10 points to cell D2 if that cell contained the word “Gryffindor.”

We’ll go back to Sprung’s example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10’s next to each Gryffindor student’s name, we’ll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.

The formula: IF(logical_test, value_if_true, value_if_false)

Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=”Gryffindor.” Make sure your Logical_Test value is in quotation marks.

Value_if_True: If the value is true — that is, if the student lives in Gryffindor — this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number.

Value_if_False: If the value is false — and the student does not live in Gryffindor — we want the cell to show “0,” for 0 points.

Formula in below example: =IF(D2=”Gryffindor”,”10″,”0″)

3. Percentage

To perform the percentage formula in Excel, enter the cells you’re finding a percentage for in the format, =A1/B1. To convert the resulting decimal value to a percentage, highlight the cell, click the Home tab, and select “Percentage” from the numbers dropdown.

There isn’t an Excel “formula” for percentages per se, but Excel makes it easy to convert the value of any cell into a percentage so you’re not stuck calculating and reentering the numbers yourself.

The basic setting to convert a cell’s value into a percentage is under Excel’s Home tab. Select this tab, highlight the cell(s) you’d like to convert to a percentage, and click into the dropdown menu next to Conditional Formatting (this menu button might say “General” at first). Then, select “Percentage” from the list of options that appears. This will convert the value of each cell you’ve highlighted into a percentage. See this feature below.

Keep in mind if you’re using other formulas, such as the division formula (denoted =A1/B1), to return new values, your values might show up as decimals by default. Simply highlight your cells before or after you perform this formula, and set these cells’ format to “Percentage” from the Home tab — as shown above.

4. Subtraction

To perform the subtraction formula in Excel, enter the cells you’re subtracting in the format, =SUM(A1, -B1). This will subtract a cell using the SUM formula by adding a negative sign before the cell you’re subtracting. For example, if A1 was 10 and B1 was 6, =SUM(A1, -B1) would perform 10 + -6, returning a value of 4.

Like percentages, subtracting doesn’t have its own formula in Excel either, but that doesn’t mean it can’t be done. You can subtract any values (or those values inside cells) two different ways.

Using the =SUM formula. To subtract multiple values from one another, enter the cells you’d like to subtract in the format =SUM(A1, -B1), with a negative sign (denoted with a hyphen) before the cell whose value you’re subtracting. Press enter to return the difference between both cells included in the parentheses. See how this looks in the screenshot above.

Using the format, =A1-B1. To subtract multiple values from one another, simply type an equals sign followed by your first value or cell, a hyphen, and the value or cell you’re subtracting. Press Enter to return the difference between both values.

5. Multiplication

To perform the multiplication formula in Excel, enter the cells you’re multiplying in the format, =A1*B1. This formula uses an asterisk to multiply cell A1 by cell B1. For example, if A1 was 10 and B1 was 6, =A1*B1 would return a value of 60.

You might think multiplying values in Excel has its own formula or uses the “x” character to denote multiplication between multiple values. Actually, it’s as easy as an asterisk — *.

To multiply two or more values in an Excel spreadsheet, highlight an empty cell. Then, enter the values or cells you want to multiply together in the format, =A1*B1*C1 … etc. The asterisk will effectively multiply each value included in the formula.

Press Enter to return your desired product. See how this looks in the screenshot above.

6. Division

To perform the division formula in Excel, enter the cells you’re dividing in the format, =A1/B1. This formula uses a forward slash, “/,” to divide cell A1 by cell B1. For example, if A1 was 5 and B1 was 10, =A1/B1 would return a decimal value of 0.5.

Division in Excel is one of the simplest functions you can perform. To do so, highlight an empty cell, enter an equals sign, “=,” and follow it up with the two (or more) values you’d like to divide with a forward slash, “/,” in between. The result should be in the following format: =B2/A2, as shown in the screenshot below.

Hit Enter, and your desired quotient should appear in the cell you initially highlighted.

7. DATE

The Excel DATE formula is denoted =DATE(year, month, day). This formula will return a date that corresponds to the values entered in the parentheses — even values referred from other cells. For example, if A1 was 2018, B1 was 7, and C1 was 11, =DATE(A1,B1,C1) would return 7/11/2018.

Creating dates in the cells of an Excel spreadsheet can be a fickle task every now and then. Luckily, there’s a handy formula to make formatting your dates easy. There are two ways to use this formula:

Create dates from a series of cell values. To do this, highlight an empty cell, enter “=DATE,” and in parentheses, enter the cells whose values create your desired date — starting with the year, then the month number, then the day. The final format should look like this: =DATE(year, month, day). See how this looks in the screenshot below.

Automatically set today’s date. To do this, highlight an empty cell and enter the following string of text: =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())). Pressing enter will return the current date you’re working in your Excel spreadsheet.

In either usage of Excel’s date formula, your returned date should be in the form of “mm/dd/yy” — unless your Excel program is formatted differently.

8. Array

An array formula in Excel surrounds a simple formula in brace characters using the format, =(Start Value 1:End Value 1)*(Start Value 2:End Value 2). By pressing ctrl+shift+center, this will calculate and return value from multiple ranges, rather than just individual cells added to or multiplied by one another.

Calculating the sum, product, or quotient of individual cells is easy — just use the =SUM formula and enter the cells, values, or range of cells you want to perform that arithmetic on. But what about multiple ranges? How do you find the combined value of a large group of cells?

Numerical arrays are a useful way to perform more than one formula at the same time in a single cell so you can see one final sum, difference, product, or quotient. If you’re looking to find total sales revenue from several sold units, for example, the array formula in Excel is perfect for you. Here’s how you’d do it:

To start using the array formula, type “=SUM,” and in parentheses, enter the first of two (or three, or four) ranges of cells you’d like to multiply together. Here’s what your progress might look like: =SUM(C2:C5

Next, add an asterisk after the last cell of the first range you included in your formula. This stands for multiplication. Following this asterisk, enter your second range of cells. You’ll be multiplying this second range of cells by the first. Your progress in this formula should now look like this: =SUM(C2:C5*D2:D5)

Ready to press Enter? Not so fast … Because this formula is so complicated, Excel reserves a different keyboard command for arrays. Once you’ve closed the parentheses on your array formula, press Ctrl+Shift+Enter. This will recognize your formula as an array, wrapping your formula in brace characters and successfully returning your product of both ranges combined.

In revenue calculations, this can cut down on your time and effort significantly. See the final formula in the screenshot above.

9. COUNT

The COUNT formula in Excel is particularly useful for large spreadsheets, wherein you want to see how many cells contain actual entries. Don’t be fooled: This formula won’t do any math on the values of the cells themselves. This formula is simply to find out how many cells in a selected range are occupied with something.

Using the formula in bold above, you can easily run a count of active cells in your spreadsheet. The result will look a little something like this:

10. AVERAGE

To perform the average formula in Excel, enter the values, cells, or range of cells of which you’re calculating the average in the format, =AVERAGE(number1, number2, etc.) or =AVERAGE(Start Value:End Value). This will calculate the average of all the values or range of cells included in the parentheses.

For reference, the average of a group of numbers is equal to the sum of those numbers, divided by the number of items in that group.

11. SUMIF

The SUMIF formula in Excel is denoted =SUMIF(range, criteria, [sum range]). This will return the sum of the values within a desired range of cells that all meet one criterion. For example, =SUMIF(C3:C12,”>70,000″) would return the sum of values between cells C3 and C12 from only the cells that are greater than 70,000.

Let’s say you want to determine the profit you generated from a list of leads who are associated with specific area codes, or calculate the sum of certain employees’ salaries — but only if they fall above a particular amount. Doing that manually sounds a bit time-consuming, to say the least.

With the SUMIF function, it doesn’t have to be — you can easily add up the sum of cells that meet certain criteria, like in the salary example above.

The formula: =SUMIF(range, criteria, [sum_range])

Range: The range that is being tested using your criteria.

Criteria: The criteria that determine which cells in Criteria_range1 will be added together

[Sum_range]: An optional range of cells you’re going to add up in addition to the first Range entered. This field may be omitted.

12. TRIM

The TRIM formula in Excel is denoted =TRIM(text). This formula will remove any spaces entered before and after the text entered in the cell. For example, if A2 includes the name ” Steve Peterson” with unwanted spaces before the first name, =TRIM(A2) would return “Steve Peterson” with no spaces in a new cell.

Email and file sharing are wonderful tools in today’s workplace. That is, until one of your colleagues sends you a worksheet with some really funky spacing. Not only can those rogue spaces make it difficult to search for data, but they also affect the results when you try to add up columns of numbers.

The formula: =TRIM(text).

Text: The text or cell from which you want to remove spaces.

Here’s an example of how we used the TRIM function to remove extra spaces before a list of names. To do so, we entered =TRIM(“A2”) into the Formula Bar, and replicated this for each name below it in a new column next to the column with unwanted spaces.

Below are some other Excel formulas you might find useful as your data management needs grow.

13. LEFT, MID, and RIGHT

Let’s say you have a line of text within a cell that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed: LEFT, MID, or RIGHT.

LEFT

Purpose: Used to extract the first X numbers or characters in a cell.

The formula: =LEFT(text, number_of_characters)

Text: The string that you wish to extract from.

Number_of_characters: The number of characters that you wish to extract starting from the left-most character.

In the example below, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. That allowed us to extract the first 4 characters of the code.

MID

Purpose: Used to extract characters or numbers in the middle based on position.

The formula: =MID(text, start_position, number_of_characters)

Text: The string that you wish to extract from.

Start_position: The position in the string that you want to begin extracting from. For example, the first position in the string is 1.

Number_of_characters: The number of characters that you wish to extract.

In this example, we entered =MID(A2,5,2) into cell B2, and copied it into B3:B6. That allowed us to extract the two numbers starting in the fifth position of the code.

RIGHT

Purpose: Used to extract the last X numbers or characters in a cell.

The formula: =RIGHT(text, number_of_characters)

Text: The string that you wish to extract from.

Number_of_characters: The number of characters that you want to extract starting from the right-most character.

For the sake of this example, we entered =RIGHT(A2,2) into cell B2, and copied it into B3:B6. That allowed us to extract the last two numbers of the code.

14. VLOOKUP

This one is an oldie, but a goodie — and it’s a bit more in depth than some of the other formulas we’ve listed here. But it’s especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet.

My colleague, Rachel Sprung — whose “How to Use Excel” tutorial is a must-read for anyone who wants to learn — uses a list of names, email addresses, and companies as an example. If you have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other, but you want the names, email addresses, and company names of those people to appear in one place — that’s where VLOOKUP comes in.

Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. Scour your data sets to make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.

The formula: VLOOKUP(lookup value, table array, column number, [range lookup])

Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung’s example that follows, this means the first email address on the list, or cell 2 (C2).

Table Array: The range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.

Column Number: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the “House” column, the second one in our table array, making it column number 2.

Range Lookup: Use FALSE to ensure you pull in only exact value matches.

The formula with variables from Sprung’s example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this example, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1. Here’s how that would work:

15. RANDOMIZE

There’s a great article that likens Excel’s RANDOMIZE formula to shuffling a deck of cards. The entire deck is a column, and each card — 52 in a deck — is a row. “To shuffle the deck,” writes Steve McDonnell, “you can compute a new column of data, populate each cell in the column with a random number, and sort the workbook based on the random number field.”

In marketing, you might use this feature when you want to assign a random number to a list of contacts — like if you wanted to experiment with a new email campaign and had to use blind criteria to select who would receive it. By assigning numbers to said contacts, you could apply the rule, “Any contact with a figure of 6 or above will be added to the new campaign.”

The formula: RAND()

Start with a single column of contacts. Then, in the column adjacent to it, type “RAND()” — without the quotation marks — starting with the top contact’s row.

For the example below: RANDBETWEEN(bottom,top)

RANDBETWEEN allows you to dictate the range of numbers that you want to be assigned. In the case of this example, I wanted to use one through 10.

bottom: The lowest number in the range.

top: The highest number in the range,

Formula in below example: =RANDBETWEEN(1,10)

Helpful stuff, right? Now for the icing on the cake: Once you’ve mastered the Excel formula you need, you’ll want to replicate it for other cells without rewriting the formula. And luckily, there’s an Excel function for that, too. Check it out below.

How to Insert Formula in Excel for Entire Column

To insert a formula in Excel for an entire column of your spreadsheet, enter the formula into the topmost cell of your desired column and press “Enter.” Then, highlight and double-click the bottom-right corner of this cell to copy the formula into every cell below it in the column.

Sometimes, you might want to run the same formula across an entire row or column of your spreadsheet. Let’s say, for example, you have a list of numbers in columns A and B of a spreadsheet and want to enter individual totals of each row into column C.

Obviously, it would be too tedious to adjust the values of the formula for each cell so you’re finding the total of each row’s respective numbers. Luckily, Excel allows you to automatically compete the column; all you have to do is enter the formula in the first row. Check out the following steps:

Type your formula into an empty cell and press “Enter” to run the formula.

Hover your cursor over the bottom-right corner of the cell containing the formula. You’ll see a small, bold “+” symbol appear.

While you can double-click this symbol to automatically fill the entire column with your formula, you can also click and drag your cursor down manually to fill only a specific length of the column.Once you’ve reached the last cell in the column you’d like to enter your formula, release your mouse to copy the formula. Then, simply check each new value to ensure it corresponds to the correct cells.

Excel Keyboard Shortcuts

1. Quickly select rows, columns, or the whole spreadsheet.

Perhaps you’re crunched for time. I mean, who isn’t? No time, no problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.

Just want to select everything in a particular column of row? That’s just as easy with these shortcuts:

For Mac:

Select Column = Command + Shift + Down/Up

Select Row = Command + Shift + Right/Left

For PC:

Select Column = Control + Shift + Down/Up

Select Row = Control + Shift + Right/Left

This shortcut is especially helpful when you’re working with larger data sets, but only need to select a specific piece of it.

2. Quickly open, close, or create a workbook.

Need to open, close, or create a workbook on the fly? The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute’s time.

For Mac:

Open = Command + O

Close = Command + W

Create New = Command + N

For PC:

Open = Control + O

Close = Control + F4

Create New = Control + N

3. Format numbers into currency.

The numbers will automatically translate into dollar amounts — complete with dollar signs, commas, and decimal points.

Note: This shortcut also works with percentages. If you want to label a column of numerical values as “percent” figures, replace “$” with “%”.

4. Insert current date and time into a cell.

Whether you’re logging social media posts, or keeping track of tasks you’re checking off your to-do list, you might want to add a date and time stamp to your worksheet. Start by selecting the cell to which you want to add this information.

Then, depending on what you want to insert, do one of the following:

Insert current date = Control + ; (semi-colon)

Insert current time = Control + Shift + ; (semi-colon)

Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).

Other Excel Tricks

1. Customize the color of your tabs.

If you’ve got a ton of different sheets in one workbook — which happens to the best of us — make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month’s marketing reports with red, and this month’s with orange.

Simply right click a tab and select “Tab Color.” A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.

2. Add a comment to a cell.

Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.

3. Copy and duplicate formatting.

If you’ve ever spent some time formatting a sheet to your liking, you probably agree that it’s not exactly the most enjoyable activity. In fact, it’s pretty tedious.

For that reason, it’s likely that you don’t want to repeat the process next time — nor do you have to. Thanks to Excel’s Format Painter, you can easily copy the formatting from one area of a worksheet to another.

Select what you’d like to replicate, then select the Format Painter option — the paintbrush icon — from the dashboard. The pointer will then display a paintbrush, prompting you to select the cell, text, or entire worksheet to which you want to apply that formatting, as shown below:

4. Identify duplicate values.

In many instances, duplicate values — like duplicate content when managing SEO — can be troublesome if gone uncorrected. In some cases, though, you simply need to be aware of it.

Whatever the situation may be, it’s easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values

Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.

In the example above, we were looking to identify any duplicate salaries within the selected range, and formatted the duplicate cells in yellow.

In marketing, the use of Excel is pretty inevitable — but with these tricks, it doesn’t have to be so daunting. As they say, practice makes perfect. The more you use these formulas, shortcuts, and tricks, the more they’ll become second nature.

To dig a little deeper, check out a few of our favorite resources for learning Excel. Want more Excel tips? Check out this post on how to create a pivot table with medians.

0 notes