Simple Math and Excel Tips

Excel Shortcut - Paste visible cells only,how to paste across filters without ruining your dataset

2014-10-26T19:30:00.002-07:00

If you've worked with large data sets which are constantly changing in Excel then chances are you've needed to paste a value across a set of filtered cells.

There’s a shortcut for this that will enable you to paste across a set of filtered values without having to paste one-by-one on the filtered data.

The answer is simple, use the shortcut Alt ;

By pressing these two keys at the same time you can select only the visible data and then use the Ctrl V shortcut to paste copied values.

A quick walk-through:

I have an excel file with population by country.

What if for some reason Afghanistan and Zambia merged and formed a new country but kept the name Afghanistan. I might want to use a filter to pull up the two countries.

Copying and pasting the value down across the filter risks pasting “Afghanistan” across the data filtered in between.

Instead we can copy an instance of Afghanistan (Ctrl+C), then highlight (select) the data and press Alt and ; at the same time. The data set should then look like below:

Now finally just press Ctrl+V to paste the values to only the visible cells.

The integrity of the final data-set should be maintained when unfiltered.

Shortcut of the week: Help

2013-09-04T17:52:00.003-07:00

When you're stuck trying to figure out how to do something in Excel, sometimes it's easy to find your answer with the help feature. To access the help menu simply press F1 on the keyboard.
From here you can search about your quandary, look for Excel templates, or find out how to use more advanced features in Excel.

Excel Keyboard shortcut of the week

2013-08-25T15:57:00.000-07:00

To quickly add comments to cells, hold shift and press F2. This works for editing the existing comments as well.

Excel and the two most powerful uses of F4

2013-08-25T15:54:00.000-07:00

The F4 key can contribute to greater productivity in Excel. This post will examine two quick ways which this key can be used to help users work faster in Excel

Toggle through cell referencing: F4 makes it faster

When writing a formula where absolute or other cell referencing is required the user does not need to type dollar signs around the cell references. Instead F4 can be used to toggle through referencing options.

Take the below spreadsheet for example:

In the above example the user would be trying to calculate the average selling price of produce given a desired gross margin percentage. The formula used above is correct, however it cannot be copied to the cells directly underneath as it is now.

=D6*(1+D2)

The above formula needs to use an absolute reference (dollar signs) around D2, then it can be copied down.

=D6*(1+$D$2)

The user could type them manually but it is often easier to hit the F4 key to add the absolute reference.

While typing the formula use F4 to set cell references, the user would hit F4 immediately after typing D2 and before the parenthesis.

Hit F4 once for absolute reference ($D$2)

Twice will fix the row (D$2)

Thrice will fix the column ($D2)

And four times will reset the referencing to default (D2)

If the formula has already been typed out, left click the reference and hit F4 to toggle.

It doesn't seem like a big time saver but if you’re writing a lot of formulas it will be.

Repeat the last action with F4

Often times users repeat tasks continuously, some situations can call for the use of F4.

If the below spreadsheet had a lot of comment cells that we wanted erased we can do it in no time using F4.

The way some users would go through a worksheet and delete comments would be to right click each commented cell and go to the delete comment option.

Of course all the cells could be highlighted and comments deleted at the same time, but what if some needed to be kept and for the sake of this example pretend I didn't tell you that.

When the user completes this action once, they can go to other cells that they want to remove comments on and hit F4. (Note that F4 is just repeating the last task carried out in Excel) This method can work well if a worksheet has to be formatted manually.

Windows Key Shortcuts

2013-08-17T14:28:00.003-07:00

A little bit off topic but a bit of a compliment to Excel are simple windows tips. Since I don't want to write another blog for windows tips, here are some of my most used windows shortcuts.

The windows logo key (between the left ctrl and alt keys) is something I have been overlooking for years. End users can shave a little time off many tasks each day using shortcuts with the key.

Over time those little time savings will add up big.

Use the following shortcuts with the windows key for common tasks:

Windows Key – Pulls up the start menu, hitting it again will minimize the start menu.

Windows Key + E – Opens “Computer” or “My Computer”

Windows Key + D – Shows the Desktop

Windows Key + F – Search for files or folders

Windows Key + L – Lock the computer

Windows Key + R – Open the Run dialog box

Windows Key + T – Toggle through menus on the task bar

Windows Key + Tab – 3D toggle view of open programs

Windows Key + Any Arrow Key – Shifts the active window position

Windows Key + Pause – Open systems Properties dialog box.

Please note, these work in Windows 7, some of the shortcuts do not work in older versions of Windows.

Hiding Formulas and ugly calculations

2013-08-15T19:20:00.001-07:00

Sometimes we have numbers in our spreadsheets that we don’t necessarily want viewers to see, maybe they're subjective or used in calculation of sensitive information.

We sometimes need to use them in calculations but need to be able to hide them. Look below for a custom format that will hide your input but keep it available for calculations. If you want to completely hide the calculation you'd have to do some additional locking down of Excel. As the formula will still be visible in the formula bar. (I will cover this in a future blog post)

The custom format is simply ;;;

To apply the format simply highlight the cells that you want to apply it to and hit ctrl+1, this will bring up the cell format menu.

Then go down to “Custom” on the “Number” tab, and enter ;;;.

Hit enter, then you’re done.

Excel Format Cells Shortcut

2013-08-14T21:11:00.002-07:00

Often times we’ll have to change the way our data is displayed in our spreadsheets, to do this we can use the format cells shortcut. (Ctrl+1)

First we select the cells which we want to format, then press Ctrl and 1 at the same time.

You can change how the numbers are displayed in the Number tab, the sample box on the right will show you a preview of your data.

The Alignment Tab

Here you’ll see options on how to control content orientation in the selected cells. Control alignment, wrap, text direction and orientation.

The Font Tab

Use the font tab to adjust font family, style, size, effects, and color.

The Border Tab

Pretty self-explanatory, select the border style, color, and outline.

The Fill Tab

Select your background color and fill effects.

The Protection Tab

You can lock or hide formulas in cells after the workbook is protected.

Don’t be lulled into a false sense of security by worksheet protections; anyone can crack an Excel document if they want it bad enough.

Now you should be familiar with the Excel Format Cells options.

Excel Baby Steps: Understanding Cell Referencing and Basic Formulas

2013-08-14T20:58:00.000-07:00

Before you start writing complicated formulas in Excel it is imperative that you understand how cell referencing works.

To make this example easier to understand you can download the attached file and follow the examples in this post. Click Here for the file.

First let’s understand the setup of an Excel spreadsheet. Let’s think of it as a grid with the X axis being columns and the Y axis being rows.

Columns are designated with letters, where rows are marked by numbers. Refer to the graphic below.

To reference a cell we would follow the XY framework with the column letter(s) first, and the row number second. For instance A5 would reference the first column and the fifth row.

Excel formulas and Cell Referencing

First of all, when typing a formula in excel an = sign must precede any argument or logic, for instance if we wanted to add cells B5 and B6 we’d type =B5+B6. It works the same for other basic functions as well such as subtraction, multiplication, division, and so on.

Relative and Absolute Referencing

The way we referenced a cell earlier, A5, is refered to as a relative reference. Meaning if you copy or drag a formula containing those references they will move in relation to the cell position. Let’s add a commission calculation to the sample file.

Select cell C2 and type COMMISSION

In cell C3 type =.05*B3

Copy cell C3 (with the cell selected right click and select Copy, or press Ctrl+C)

Left click on cell C3 and move the cursor down to C12 this should highlight all the relevent cells.

Now paste the formula (press Ctrl+V, or right click and select Paste)

Look at the formula in the formula bar and you can see that the cell reference moves down with each row.

Now suppose this company wants to assign a percentage of Sales Revenue to a cost such as Marketing, for budgeting and planning purposes. Let’s use this as an example for absolute referencing.

Add a row above row 1. (Left click on the number 1, then right click and select insert) or (highlight any cell in row 1 and press shift+spacebar, then press Ctrl and Shift and + at the same time)

In cell A1 type MARKETING BUDGET RATE

In cell C1 type .05 (Assuming they’ll use 5% of their Sales Revenue to spend on Marketing) I used C1 because we don’t have to adjust column widths this way.

In cell D3 type MARKETING BUDGET

For this column we do want to autofit our label. We can do this by double clicking the line between the column D and E labels along the top, or by selecting the entire column (Ctrl+Spacebar) and pressing Alt+H+O+I.

Now we’re ready for our formula, select cell D4

Type =B4*$C$1 in cell D4. See exlplanation of dollar signs below.

To apply this formula to the entire data set we have to lock the cell reference (make it absolute) to lock in a cell reference put a $ sign in front of the row or column you want to lock in our example we want to lock both row and column, $C$1.

Copy the formula in D4 and paste it to cells D4 through D13. You should notice that C1 is in the calculation on each row.

Suppose we wanted to change the amount for the marketing budget. Simply select cell C1 and type .035. You should now see different numbers in column D than before.

This should demonstrate the power of absolute referencing in spreadsheet automation. (you don’t have to manually adjust formulas like you would for relative references) Of course every situation is different and we have to use these techniques when each is appropriate.

We should change the commission to an absolute reference with an adjustable number at the top as well. (Like the marketing budget) If everyone is being paid the same we will want the ability to change the rate easily.

Select Row1 and insert a row.

Type COMMISSION in cell A1.

In cell C1 type .05

Go to cell C5 and type =B5*$C$1

Copy C5 and paste in cells C5 through C14.

Totaling The Columns

Let’s cap off the example by totaling each column on our spreadsheet. We’ll use a SUM function.

Select cell A16 and type TOTAL.

In cell B16 type =SUM(B5:B14)

Copy cell B16

Paste in cells C16 and D16

You should now have a basic grasp of excel cell referencing and basic formulas.

Defining and Using Named Ranges in Excel 2010

2013-08-11T23:26:00.000-07:00

Sometimes you want to name ranges of cells to make them easier to refer to in Excel for formula intensive worksheets.

I don’t typically use “named ranges” because I find it easier to see what the formula is referencing without them. However I recently learned that many people use names in their worksheets. It’s also a good thing to know how to look for “named ranges” in the workbook as well.

Defining Named Ranges

Once you have data that you want to create a named range for, doing it is easy.

Select the formula tab and click “Define Name”.

Click the button to the right of the “refers to” box at the bottom of the pop-up.

Then select the cells you want to have in your range, click and drag across the range of cells.

For this example I selected the data under the name in column A.

I will make one more named range as an example in the “Sales” column so I can demonstrate potential usefulness of named ranges.

Now if I want to do a calculation of average sales I can simply type the formula =AVERAGE(Sales)

The result will be the average of the “Sales” range that I just created.

Now you can use the Sales named range in any similar manner.

Another important point to know is how to find a list of all the named ranges in the workbook. This is simple yet very useful.

Just click on the name manager item at the top of the screen when in the formula tab.

You should get the following pop-up:

If you’re taking over a workbook from someone else this feature can be very valuable because you will be able to tell if they’re using named ranges and where they are located in the workbook.

The SUMIF: A very simple yet powerful formula.

2013-08-11T22:48:00.002-07:00

Learning this formula can open up a new world to novice Excel users. I've used the SUMIF function in everything from time-sheets to financial statement

compilation.

It’s very easy to set up and it can have a profound effect on how you record and report information.

On the top of the spreadsheet we have a summary section where we’ll use SUMIFs to calculate the sales for each region.

Starting in row 15 we have a list of sales transactions with each record containing the Sale Number, Sale Amount, and Region Number. There are a total of 50 transactions listed.

Let’s create the SUMIF formula for our summary section.

In cell C3 I typed the following formula:

=SUMIF($D$16:$D$65,A3,$C$16:$C$65)

Refer to the graphic below for an explanation of the formula.

Next, we can copy the formula in C3 (Ctrl+C) and paste it (Ctrl+V) down the rest of the cells in our summary (C4 through C12). In C13 we can simply sum each region to get a total. In C13 I typed =SUM(C3:C12)

That's a basic SUMIF function.

How to create an Excel scatter plot and how to do it with text in the X axis

2013-08-10T15:02:00.001-07:00

Creating a proper scatter plot in Excel can be more difficult than you’d think. If you want text at the bottom of the chart you have to use a line graph and remove the lines.
If you do it this way you can update categories and data with ease. Follow below for a step by step guide to scatter plots with text in the x axis:

How to create a scatter plot:

To demonstrate, here is a sample data set I made up.

Create the scatter plot by selecting the entire data set from B3:E10.

A. Select the “Insert Tab” to the right of the “Home Tab” at the top right of the screen.

B. Click Scatter in the “Charts” section. Select “Scatter with only Markets”.

Transforming the X axis to Text Categories

Notice the x axis is numbers, even though the data is text. To fix this change the graph to a line graph

Select the graph by left clicking. You’ll see a green area appear in the upper left portion of “tabs”. Select the design tab, left click the “Change Chart Type” option. Select the line graph and the line graph with markers. Then click OK.

Your graph should now look like below:

Remove the lines from the graph. To remove the lines left click each line one by one and follow these steps:

1. Right click and select “Format Data Series”

2. Select “No Line” in the Line color tab.

3. Close the “Format Data Series” menu.

The chart should look like this:

Now remove the zero values from the chart.

1. For each series, select the series by left clicking a data point and go to “Design” tab in the green chart tools menu at the upper right.

2. Click “select data”

Select show empty cells as “Gaps”

Click OK. Then Click OK again on the Select Data Source Box to accept changes.

Your finished scatter plot should look like the one below.

Moving Beyond Basics: Nesting VLOOKUP and MATCH Functions

2012-07-03T10:27:00.002-07:00

Nesting a MATCH function within a VLOOKUP can enable you to eliminate manual adjustments to your VLOOKUP functions when working in spreadsheets with Excel.

For reference purposes you can download the sample Excel file by clicking here.

Before you try these instructions, make sure that you have a mastery of the basic VLOOKUP formula; you can reference this earlier post.

Open the sample file and insert a new tab. (You can hold shift and press F11 to do this quickly)

Right click the new tab and rename it “Summary”. Your screen should look something like this:

Go to the “Data” tab and copy all the names of the countries that begin with “A”, paste the values on your "summary" sheet (starting in cell B6) add a COUNTRY label to the top of the list, then go one cell up and one cell right and type “POPULATION”. Under population add years going from left to right, from 2011 to 2020. You should now have something similar to below.

In cell C6 type in the following:

=VLOOKUP($B6,Data!$B$3:$L$201,MATCH(Summary!C$5,Data!$B$3:$L$3,FALSE),FALSE)

If you took a shortcut and cut and paste the formula, you will have to change the font color for the result to show.

To break down the formula in order to understand what was just entered, refer to the picture below.

Your sheet should look like the picture below.

Highlight C6 and copy it (use ctrl+C, or right click and select Copy), then highlight cells C6 through L15 and paste (ctrl+V, or right click and select Paste). Your sheet should now look like the picture below.

Now you should know the basics of nesting the MATCH formula within the column reference section of a VLOOKUP. This will make your spreadsheets more adaptable and easier to update.

VLOOKUP a Starting Point for Excel Mastery

2012-06-19T18:12:00.000-07:00

Chances are, if you’ve worked with Excel in a business function, you’ve had to navigate, manipulate, and summarize large sets of data.

When working with spreadsheets with large sets of data the VLOOKUP is one of the most important functions you can learn. You can use it to build a foundation of Excel knowledge. Below I am going to show you how to perform VLOOKUPs.

Let’s begin.

You may want to download the sample file by clicking here.

I’m working with UN population data for this example.

I want to create a summary of the population in North America using VLOOKUPs.

First let’s insert a new tab in our worksheet and name it “summary”.

Right click on the tab named “Data”.

Select “Insert” from the menu.

Click OK. (The “Worksheet” option should be the default)

Now let’s rename the tab.

Right click on the worksheet labeled “Sheet1”

Select the “Rename” option

Type “Summaryy”, then click away from the tab.

Your screen should now look like this:

Let’s create a summary of the population in North America. For our purposes we’ll consider The United States of America and Canada will comprise North America.

Type the following country names in cells B2 and B3:

United States of America

Canada

Type in years in cells C1 through C11. Start with 2011 and end with 2020.

Your spreadsheet should now look like below:

Now we do a VLOOKUP to find the population of the countries for each of the years.

In cell C2 type =VLOOKUP(

Then select B2, this is the text the formula will look for and return a value based on it. In this case it’s United States of America.

Type a comma

Then go to the “Data” tab and select the whole data set from B3 to L201.

Type a comma

Type 2, this is the column of the data set which the formula will return. In this case column 2 has values for 2011.

Type a comma

Type “FALSE”, this will tell the formula to return an exact match for “United States of America”.

The finished formula should look like this: =VLOOKUP(B2,Data!B3:L201,2,FALSE)

The next step is to make sure the referencing on the formula will work if we copy in paste it in the next row, for Canada.

Click in the formula on the “B2” segment, tap the F4 key until you see $B2, this means that the formula will always reference column B, even if you copy and paste it.

Then click on the”B3” segment, tap F4 until it looks like: $B$3, this will lock in the starting point for the formula’s reference range. Do the same for the “L210” segment of the formula.

Your formula should now look like this: =VLOOKUP($B2,Data!$B$3:$L$201,2,FALSE)

You should now be able to copy and paste the formula. The spreadsheet should look like this:

Copy the formula and paste it in C3 for Canada’s population in 2011. Do the same for the year 2012, notice that it didn’t work, that’s because the formula is still referencing column 2, we need to change it to 3.

Change the formulas in column D to reference column 3, like =VLOOKUP($B2,Data!$B$3:$L$201,3,FALSE)

In later posts we’ll cover nesting a MATCH function within a VLOOKUP to completely automate the VLOOKUP function.

For now copy the formula and change the column manually in each. 2013 would be column 4 and so on.

Your ending spreadsheet should look like this:

You should now have a basic understanding of VLOOKUP formulas.

How to Quickly Create Pivot Tables in Excel 2010

2012-06-18T10:02:00.002-07:00

Pivot tables are amazing things when one learns how to work with them. This post will simply cover how to create a pivot table from a raw data set.

First, I am starting with my dataset, which is a list of all the countries of the world and their population projections from 2011-2020. The data comes from the United Nations.

Click Here to download the sample file.

Why would I want to Pivot the data? If I want to analyze the data in any way or look at it differently than how it is displayed in the original format, I will want to put it in a pivot table.

Let’s begin; it will be over before you know it.

Then I go to the insert tab and click the create pivot button. A pivot table menu should pop up like the one below.

Notice that your selection is in the Table/Range box. Most of the time you want the pivot to be on a new sheet, so make sure the “New Worksheet” radial button is marked, then hit OK.

Now you should see your pivot table, you may want to enable classic view if you have worked in pivot tables in previous versions of Excel. Classic view enables you to drag and drop fields into the pivot table. I prefer this method over the new on in 2010.

Enable Classic View

Right click anywhere in the pivot table and select “PivotTable Options”, then go to the display tab and make sure that classic view is checked. See below:

Now you should be able to drag the fields (columns from the data source) into the places where you want them in the pivot table.

Click and drag “COUNTRY” to the “Drop Row Fields Here” area.

Now repeat with the “YEAR” field and drop it in the “Drop Column Fields Here” area.

Finally click and drag the “POPULATION” field to the “Drop Value Fields Here” area of the pivot table.

Congratulations! You should now be done, but if for some reason it does not use a “sum” in the values are you may have to manually change it. To do this, right click on the field that is highlighted above (Yours probably says “Count of POPULATION” if you’re doing this) and select “field value settings” from the menu. Make sure you select “Sum” from the list of options. (You can use the others if they suit the specific thing you’re using the PivotTable for)

This is the first in what will surely be a series of posts about PivotTables.

How to Quickly Create Tables in Excel 2010

2012-06-16T19:56:00.000-07:00

Sometimes it’s advantageous to put our data in tables in Microsoft Excel. If you want to reference, analyze, format, sort, or filter, you will probably want your data in a table. To quickly create a table, follow these simple steps.

Step1: Type in, or import your data, in this case I’ve taken the liberty of creating a set of dummy data as an example.

Step2: Highlight your data, then go to the “Insert tab” and left click on table. Alternatively you can press ctrl+t and pull up the same menu.

You should now see the “Create Table” Dialog box. The range which you had selected should appear in the box, like in the picture below.

Note that the “My table has headers” option is checked.

Hit okay and you should now have data formatted in a table. It should look similar to below.

Now that you have a table there are many things you can do with your data, those should be covered in later posts.

Multiplying 3 Digit Numbers with Each Other in 30 Seconds

2012-06-16T14:21:00.002-07:00

Part of my quest to conquer evil standardized testing has included the search for better multiplication methods. Recently I found the method below and thought I would work to spread the word.

Using the cross or vertical technique will save you half the time and scratch paper used by the standard technique most students are taught in grade school.

Cross or Vertical Multiplication

Use two, three digit numbers, let's multiply them.

Step 1: Vertically multiply the first two digits in each number.

Step 2: Cross multiply the first two digits in each number, then add their products, place the unit digit of the result in the solution and carry anything over. If carrying forward I usually write the tens digit first and then the unit digit, it’s just a personal preference)

Step 3: Multiply the three digits in each number with each other, then add the products. Note that each digit only has to be multiplied once and that we cross multiply when we can and multiply vertically when we can’t.

Step 4: Cross multiply the last two digits in each number and add their products, again remember to carry forward when appropriate.

Step 5: Vertically multiply the last digit in each number, writing the unit digit to the solution and carrying forward.

Step 6: Add down from right to left, carrying over if appropriate.

That's it! You're Done!

How to Easily Find Doubling Rates for Investments with Continuous Growth

2012-06-15T19:01:00.002-07:00

It’s not often that you have to do a financial calculation and don’t have a calculator to help you. But in case something happens to send us back to the stone age it would be good to know the rules of 72, 70, and 69.3.

Sure we can use the following formula to find the doubling time of an investment with periodic compounding, but could you do this in your head?

Probably not.

The Rule of 72

Particularly useful for interest rates above 6% and below 12%.

Simply divide 72 by the continuously compounding interest rate:

For a rate of 9% we’d have 72/9 or 8 years.

It’s not super accurate but it will give you a very solid approximation.

For low interest rates and daily compounding use 70 or 69.3 as the numerator.

For more simple math and Excel tips and tricks visit my Tips and Tricks page on infopresented.com

Quick Percentage Change Calculation

2012-06-15T16:52:00.000-07:00

A colleague taught me a trick to calculate percentage change quicker than the traditional way at the start of my last job. Below is the formula, it basically just cancels out steps that I’d done before, but for some reason I’d never thought to do it this way.

(Ending value/Beginning Value)-1

Example, last quarter I sold 100 units and this quarter I only sold 75, thus I had a decrease of 25, I can calculate the percentage change really quick.

(75/100)-1 = -.25 or a 25% drop.

This little trick works, and if you use it, you can save a lot of time in the long run.

For more math and Excel tips visit: www.infopresented.com

The 3rd Graders Guide to Multiplying Two Digit Numbers

2012-06-15T16:42:00.001-07:00

While I was recently preparing to take my GMAT I couldn't help but feel incredibly stupid trying to do math in my head and on paper. The fact is I’d become completely enslaved by my calculator. Only five years out of college and I could barely multiply and divide. That’s when I found this nice little tidbit of information to easily multiply two digit numbers by other two digit numbers.

How to multiply two digit numbers together in less than 6 seconds:

Step 1: Find the first piece of the solution.

Multiply the first two digits in each number.

These are the first two digits of the solution.

Insert a blank after the result.

Step 2: Find the last number of the solution.

Multiply the last two digits in the numbers of the problem. Then write the result after the blank. (Note that if the result is itself a two digit number we must carry a digit over the blank)

Step 3: Fill in the blank.

This step can be the trickiest of the process (although it’s still simple).

First multiply the outside digits of the problem.

Then multiply the inside digits of the problem.

Add the results together and you have your number that goes in the blank.

Note that if the result is itself a two digit number we must carry a digit over a place, also if you had to carry a number over the blank then we add that to the solution as well.

See below:

Step 4: Add down

The method works for any set of two digit numbers.