Add Values Between Two Dates

If you are keeping records in an Excel spreadsheet that contains a column for dates and another column for some figure that can be added up, such as sales, you can easily use a formula to add values between two dates.

The formula uses the SUMIFS() equation, which basically adds up a range of cells based on a certain set (or sets) of criteria. In this case we will use two dates as the start and end point to tell the formula to add up any values that are found in between those dates.

Add Values Between Two Dates

Add values between two dates with SUMIFS

In the image above, there are dates in column A, and a list of number we want to add in column B.

You can see that there is also a little table in columns D:F that show the end result. Here’s how you can get there.

In cell E2 (the January total), you would enter in the following formula:

=SUMIFS($B$1:$B$12,$A$1:$A$12,">="&E$4,$A$1:$A$12,"<="&E$5)

You can then drag that formula over to cell F2 (or however far across you need it to go) to get totals for other months.

Step-by-step, here is what that formula is doing:

  • $B$1:$B$12 – Defines the range of cells to be added if they meet the criteria to be defined later in the equation.
  • $A$1:$A$12 – Defines the range of cells that the first criteria should apply to. In this case it is the range of dates.
  • “>=”&E$4 – Defines the “start date”, which in the example above is entered into cell E4. However, if you wanted to enter the start date directly into the formula, you could replace “>=”&E$4 with “>=1/1/2014″, or whatever your start date is.
  • $A$1:$A$12 – Defines the range of cells that the second criteria should apply to. In this case it is the same range that the first criteria should apply to. You could define a different range if you needed to.
  • “<=”&E$5 – Defines the “end date”, which in the example above is entered into cell E5. Just like the start date, you could enter “<=1/31/2014″ directly into the equation if you wanted to.

A couple things to point out about this equation:

  • The range of dates do not need to be in any particular order. Notice in the example how the January dates are all sequential, but the February dates are out of order. The formula would have produced the same results if the list was sorted by column B, or if it was in no particular order whatsoever.
  • The SUMIFS() will not work in a Excel 97-2003 workbook. Instead, you can use the SUMPRODUCT() equation to achieve the same results.
    • =SUMPRODUCT(($A$1:$A$12>=E$4)*($A$1:$A$12<=E$5),$B$1:$B$12)
  • Notice the count row under the total row? This is achieved by using the COUNTIFS() formula, which is very similar to SUMIFS(), except that it counts the number of cells that meet certain criteria rather than sum up a range of corresponding cells. This could be a useful addition to the spreadsheet if, for example, you wanted to get an average sale amount.
    • =COUNTIFS($A$1:$A$12,">="&E$4,$A$1:$A$12,"<="&E$5) – This basically looks at the dates in A1:A12 and counts them if they fall in the range of dates given in E4 and E5.
  • You can easily change the dates that get added by changing the values in E4 and E5 (and F4:F5). This will let you  add up all the amounts on the same day, during a week, month, year, or whatever time period you choose. 

What applications would you use this sort of formula for?

Generate a Random Character String

You may find yourself faced with a need to generate a ton of random strings of characters.

For example, you may have a list of usernames that need passwords – you could be lazy and say everyone’s password is password, but I will assume you know better than that!

Generate Random Number String

To generate a random number string in Excel, simply use a formula like this:

=RANDBETWEEN(0,9)

This will randomly display a number between 0 and 9 each time the page is refreshed.

Generate Random Uppercase Letter String

=CHAR(RANDBETWEEN(65,90))

This will randomly display a letter between A-Z.

Generate Random Lowercase Letter String

=CHAR(RANDBETWEEN(97,122))

This will randomly display a letter between a-z.

Generate Random Symbol String

=CHAR(RANDBETWEEN(33,47))

This will randomly generate one of the following symbols:
!”#$%&’()*+,-./

*Note: the CHAR() formula in Excel will generate a lot of other symbols as well. However, if the purpose is to create a password that someone may have to type on their keyboard, you want to keep it simple and avoid symbols like ¶, Œ, or ©.

Generate a Random Password

Good passwords tend to have at least eight characters and are a mix of uppercase letters, lowercase letters, numbers, and symbols. To do this, simply combine the above formulas into one formula by simply adding an ampersand (&) in between each formula, like this:

=RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(33,47))&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(33,47))

This would create something like 6Ib&4Rj/ or 8Bs%3Xa+
You can have any combination of letters, numbers or symbols that you want by moving the formulas around. There’s also nothing saying you couldn’t have a longer password string by chaining on even more randomness to the formula.

A word of caution! If you are using this to generate a list of passwords, for example, be aware that the list will automatically change every time you refresh your worksheet, or change the value of a cell, etc. To ensure you don’t lose these values once they’re created, select the list of random passwords, copy and paste them as values. This will prevent them from updating again.

Protect a Worksheet Except for Individual Cells

If you have ever used Excel to gather information from people in a pre-defined form setup, you know it can be frustrating when the responses contain added rows, columns, comments out of sight, etc. The purpose of taking the time to set up a form is so that you can get your data in a consistent format/layout.

It’s actually pretty easy to protect your worksheet from people who might want to get creative with your hard work.

Here’s the steps to lock all cells in your workbook, except for the few individual cells you want people to input information to.

  1. Click the box to the left of column A (in between column A and row 1). This will select all cells.
  2. Right click the same box – select “Format Cells” then click the “Protection” tab.
  3. Make sure the “Locked” check box is checked.
  4. Click “OK” These first few steps just made sure that all cells are locked. See steps 5-6 for unlocking individual cells.
  5. Right click just the cell(s) you wish to have editable by the end user (i.e. unlocked).
  6. Select “Format Cells” then click the “Protection” tab. Then un-check the “Locked” box. This will “unlock” the cells that you want the end user to be able to edit.
  7. In newer versions of Excel (2007+), click the “Review” tab, then click “Protect Sheet” under the “Changes” group.
    In older versions of Excel (97-2003), this is found under the “Tools” menu, then “Protection”.
  8. If you wish, enter a password in the prompt to be used to un-protect the sheet.
    Note: If no password is entered, anyone can repeat step #7 to un-protect the sheet.
  9. Under “Allow all users of this worksheet to:” un-check all boxes except for “Select unlocked cells” then click OK.

Users will now only be able to select and input into the cells that were unlocked in step #6. They won’t be able to insert or delete columns or rows, add comments, or any of the other actions listed in the Protect Sheet window.

It might also be a good idea to format your worksheet in such a way that the end user will know which cells can be (or should be) edited. For example, a different cell background or border color might be a good visual cue to someone that a cell is editable. Alternatively, you could type some generic text (i.e. “First Name”, “Last Name”, “Address”, etc.) into the cells, so  the users will know what is expected of them in the cells.

Now what happens if you enter a password, want to go back and edit your worksheet, but forget what the password was? Well in most cases, you are stuck. You won’t be able to edit anything other than the unlocked cells. That is, of course, unless you know how to crack the password.

The Mystery of Excel’s Too Many Different Cell Formats

If you have ever received the “Too many different cell formats” error message in Excel, stop what you’re doing, take a look around your workbook and ask yourself “are all these cell formats really useful?”  After you answer the inevitable “no”, smack yourself in the back of the head for creating such a mess!  Just kidding, don’t actually smack yourself, but apologize to your co-workers for making them put up with your “creativity”.

The reason why you got this error is because an Excel file can only have approximately 4,000 different combinations of cell formats.  At first this might seem like a lot, but think about all the unique formatting characteristics that can be applied to a cell.

  • Fonts: including the font, font size, bold, italic, underline, strikethrough, superscript, subscript, color, etc.
  • Borders: including which side of the cell has a border (top, left, right, or bottom), border color, border thickness (or weight), etc.
  • Fills: including fill color, and patterns.
  • Number formatting: such as General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, or Custom.  Not to mention the number of decimal places shown.
  • Alignment: Top, bottom, left, right, centered, centered across selection, indented, orientation degrees, wrapped text, shrink to fit, merged cells, text direction, etc.

Any unique combination of the above cell formats counts towards the 4,000 limit, however if several cells share exactly the same formatting, it only counts as one.

For example, assuming that all other formats are the same, a 3×3 cell “boxed” with a border going around it would have nine different cell formats, considering that no cell in the group would share the same side border formatting (see below).

Top & Left Top Top & Right
Left None Right
Bottom & Left Bottom Bottom & Right

 To fix this, simplify the formats of the cells in your file, by using some of the suggestions below:

  • Use only one or two standard fonts.  For example, if you want your headings to be bold, and the data to be in a regular style, do so consistently.
  • Use consistent borders in your worksheets.  Maybe just box in headings and your data points to make it easier for your files users to read.
  • Clear out unnecessary fill colors and patterns.
  • Make sure your numbers are consistently presented (i.e. percentages are shown as such with the same number of decimal points, dollars are shown in the currency, or accounting format, etc.).

Also, consider clearing the formats of blank cells.  To do this, click Edit | Go To | Special | Blanks.  This will highlight all cells that do not contain data.  Next, click Edit | Clear | Formats (or All).  This will clear all the blank cells of any formatting that have been applied to them.  Be careful however, as this could have unintended consequences if you’ve filled the cells in a row or column to act as a “border” between sections in your worksheet, or if you’ve used the Center Across Selection formatting.

After done simplifying your cell formats across your entire file, save, close, and then re-open before adding any new cell formats.

Transition To Excel 2007

I’ve gotten a few requests for a way to help transition to Excel 2007 from earlier versions of Excel.  As anyone who first tries using Excel 2007 will notice, the traditional File, Edit, View, etc. menus are no longer available.  Instead there is a ribbon, which acts like the old menus.

To help Excel 2007 newbies make the transition, I’ve designed a quick reference mapping of the Excel 2003 menus to the Excel 2007 ribbon on a convenient mouse pad.  For example, if you previously went to Format | Cells to access the format cells menu, you would go to Home Tab | Cells Group | Format in Excel 2007.

If you think it would help, check it out at CafePress.com by clicking on the image below.

Excel 2007 Mousepad

Excel 2007 Mousepad

How to Recover Lost Excel Passwords

Let me preface this article by saying that this will not help you recover lost data, or gain access to protected data that you otherwise wouldn’t have access to.  What it will do is allow you to unlock a password protected worksheet in Excel, so that you can edit it as necessary.  Simply put, if you don’t already have access to the worksheet, this macro will not help you.

Now that the disclaimer is out of the way, let’s figure out why people password protect worksheets to begin with.  By protecting a worksheet and the contents of locked cells, users can keep themselves or other users from:

  • accidentally (or intentionally) removing formulas, or other contents of locked cells
  • adding or deleting rows and columns
  • changing cell, column or row formats
  • sorting data
  • using AutoFilter or PivotTable reports
  • editing objects or scenarios


Protecting a worksheet will not keep users from

  • editing any unlocked cells in the worksheet
  • viewing all data in the worksheet, regardless of if it is in a locked cell or not

Being able to unlock a password protected worksheet is useful, when

  • you have forgotten the password on your own worksheet
  • a co-worker, or other user has password protected a worksheet that you now need to edit, and they are not available to unlock the sheet for you
  • you have a need to perform an analysis of the data in a password protected worksheet, but are unable to do so due to the locked cells
  • you would like to sort/filter the data in a password protected worksheet, or create a PivotTable report from the data

Since Microsoft Excel is not a “secure” program, it is very easy to unlock the password of a password protected worksheet.  This is because there are several different combinations of passwords that Excel will accept to unlock the worksheet.  For example, a worksheet with the password “treehouse”, can also be unlocked with the password “AAAABAABBBB/”.  The opposite is also true (i.e. protecting with the password “AAAABAABBBB/” can be unlocked with the password “treehouse”).  Go ahead; try locking a worksheet with the password treehouse, and use AAAABAABBBB/ to unlock it.

The macro code below will “unlock” one worksheet at a time, using the method used above.  It won’t provide you with the actual password someone typed in, but rather a random sequence of letters or symbols that will work to unlock the worksheet.


Sub PasswordRecovery()
'
'MACROS BY EXCELZOOM.COM
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Goal Seek to Break Even

Recently I found myself deciding if I should sell some stock in order to provide a little extra cash to my bank account.  I purchased shares of the company’s stock four times in various quantities, and at varying prices.  Due to the downturn in the stock market recently the prices dropped steadily, so I was able to purchase some of the shares at a low price.  The company has done a little better in recent months and has produced a gain for some of the shares I bought at the lower price.

The question I want to solve is: if I sell all the shares that have produced a gain, how many of the shares should I sell at a loss in order to offset the gains, and the resulting taxes?

To do this, I set up my spreadsheet like the one below:

A B C D E F
1 Shares Purchase Price Total Purchase Current Price Current Value Current Gain/Loss
2        60                65.26            3,915.60             20.79          1,247.40               (2,668.20)
3        20                17.26              345.20             20.79             415.80                     70.60
4        75                14.37            1,077.75             20.79          1,559.25                   481.50
5      100                  6.95              695.00             20.79          2,079.00                1,384.00
6
7 Total Gains                1,936.10
8 Losses to Break Even                          -
9 Shares to be Sold to Break Even
Note: these are not the actual amounts I used in my own personal situation. They are only provided for illustration purposes.

Download the example file here.

In cell F8 (labeled “Losses to Break Even” in cell E8), I have the following formula: =(F9*B2)-(F9*D2), which I want to equal the total gains amount, of $1,936.10.  Right now it is showing a zero value because I haven’t determined how many shares need to be sold at a loss to equal the gains.

To do this, select cell F8, then click Tools | Goal Seek.  In the box that pops up, enter the following:

  • Set cell: Keep as F8.  If it isn’t F8, enter F8 here.
  • To value: Enter 1,936.10, as this is the value we wish to set the losses to.
  • By changing cell: Enter cell F9, as you want to determine how many shares need to be sold in order to break even.
  • Click OK

The result will tell you that you should sell 43.54 shares in order to break even.

For more information on Excel’s Goal Seek feature, take a look at the post Goal Seek to Solve a Problem.

Changing the Default Color Palette in Excel

Excel, by default comes with a standard pre-selected color palette, which is useful, however there are times when those standard colors just won’t do.

It’s possible that your company’s colors are not included in the standard colors found in Excel.  If you’d like to use them in your workbook, you’ll need to change the standard colors.

In order to do this, select Tools | Options | Colors tab (in pre-2007 versions of Excel), or Microsoft Office Button | Excel Options | Save | Colors (in Excel 2007). 

Next, click on the color you wish to change, and click Modify.  You can choose from one of the colors on the Standard tab, or you can click the Custom tab if you know the RGB or HSL format of the color you wish to use. 

Repeat these steps as often as necessary, then when you return to Excel, you can change the fill, font, and chart colors in your workbook to your new custom colors.

Goal Seek to Solve A Problem

Microsoft Excel is jam packed with cool useful features that tend to go unused by most users, simply because they don’t know how to use them, or even that they exist.  One of these features is Excel’s Goal Seek tool.

First, some background on what it does.  Goal Seek is essentially the answer to every middle school aged kid’s math test word problem.  It is best used when you know the answer to the problem you’re trying to solve, but don’t know all the inputs.

For example, a simple word problem might be “You have 432 sheets of paper to give out to a class of 36 students.  How many sheets of paper does each student get.”  Well you know that 36 multiplied by something will give you 432, but you don’t know what that number is.  You could divide 432 by 36 to get the answer, but let’s assume that isn’t possible right now.  With Goal Seek, you need several things:

  • An answer expressed as a formula, in this case 432 is equal to 36 times something.  Don’t worry, the result of the formula doesn’t have to equal 432 yet.  That’s the magic of Goal Seek.
  • You need one part of the equation, in this case 36.


To start, type the following into your spreadsheet in the cells indicated.

 

1

 36

2

 <leave blank>

3

 =A1*A2

Next, open Goal Seek (Tools | Goal Seek, or Data tab on the Excel 2007 ribbon | What If Analysis | Goal Seek).

You are now going to be asked for three things. 

  1. Set Cell: this is the reference to the cell that contains your formula, in which you want Excel to produce your answer of 432.  In our example it should be A3.  Note: the Set Cell MUST contain a formula or function.
  2. To Value: this is the value you want in your Set Cell (A3).  The value here should be 432.
  3. By Changing Cell: Since we know that the formula in cell A3 is correct, and the value of 36 in cell A1 is correct, we want Excel to produce the answer of 432 by changing cell A2.  Type A2 in this box.  Note: the Changing Cell MUST contain a value.
  4. Click OK.

After clicking OK, Excel will attempt to find a solution to the equation.  Once it does, it will enter the missing number in cell A2 and your formula should now equal 432.

Obviously this is a basic example, and doesn’t use Goal Seek’s capabilities to their fullest extent.  Let’s take another example that looks at a company’s sales by product to get their desired level of revenue.

XYZ Company makes four different products, Product A, Product B, Product C, and Product D.  The company can produce 1,000, 750, 500, and 100 of each product respectively.  The products also sell for $15, $20, $35, and $50 respectively.  Their maximum revenue for products produced that month, therefore is as follows:

 

A

B

C

D

1

Product Production Sales Price Net Revenue

2

Product A 1,000 $15 $15,000

3

Product B 750 $20 $15,000

4

Product C 500 $35 $17,500

5

Product D 100 $50 $5,000

6

Total     $52,500

Now say the company feels that their underdog, Product D, could be producing much more.  They also want their revenues to be 60,000.  How many more of Product D will they need to produce?

To figure this out, run Goal Seek just like before, except this time, the “Set Cell” is cell D6, “To Value” is 60,000, and the “By Changing Cell” is cell B5.  After clicking OK, you’ll notice that Excel has changed the value in cell B5 to 250, indicating that you will need to increase production of Product D to 250 units from 100 in order to have revenues of $60,000, assuming everything else remains the same.

Use Excel As A Calculator

OK, I know the tag-line for this website is “…because it’s more than just a calculator”.  Of course this is in reference to the almost endless number of functions that Excel can calculate when compared to a standard calculator.  That isn’t to say though that Excel can’t calculate things just as well as a standard calculator.

For example, when balancing a checkbook, you could write out your bank balance and manually add or subtract the deposits and checks that haven’t cleared the bank yet.  Or, you could type your bank balance into Excel, type the list of checks and deposits and have Excel do the math for you.

You could also add a list of numbers together in one cell the opposite way from how you would calculate them on a calculator.  If you have the following list of numbers: 8, 17, 49, 32, 19, you would add them on a calculator by typing 8+17+49+32+19= to produce 125.  In Excel, the equals sign is on the opposite side of the equation.  You simply type =8+17+49+32+19 hit Enter and the cell will display the result, 125.

You can do this with all the familiar operators found on a calculator (add, subtract, multiply, and divide), as follows.

Operator Calculator Input Excel Formula
Add (+) 5+5= =5+5
Subtract (-) 5-5= =5-5
Multiply (*) 5×5= =5*5
Divide (/) 5÷5= =5/5

So, while Excel is more than just a calculator, it can still perform various functions like a calculator.