Lets collect some sales data!

I've decided to help the Etsy community out by providing some statistical information that is available for individuals through my shop.

Current Results based on 551 Sales
Most Sales: Saturday - 172 (31%)
Least Sales: Tuesday - 51 (9%)



How does it work?
You download the spreadsheet I have posted here, and load your Etsy information. You will see the spreadsheet does NOT load your sales information. It is only loading quantity, and date sold. I do not want there to be any question about whether I am using your information for other purposes, so I'll only collect what I need!

Once you've downloaded the spreadsheet, make sure you've downloaded all of your Etsy Sales CSV files, and then just click the import button. It will quickly pull all of your data in. Then just send the spreadsheet back to me at SimpleSolutionsExcelTips[at!]gmail.com and I will start to post the aggregate data here.

If you're interested in seeing the data for yourself, you can purchase the sales tracker from my shop!

Thanks for contributing!

Link to Sales Importer

Paste Special Operations

I'm going to take a brief interlude from the date series. I am in the middle of moving, and want to be able to spend the appropriate time to put together the next two series. Today I'm going to share a quick tip that most people should find some use for!

If you go to Edit -> Paste Special, you
will notice there are some lesser used options at the bottom "Operations".

Lets say you wanted to quickly transform this data into thousands.

In an used cell, we'll type the number 1000. Then click copy (ctrl + c...stop using that mouse).

Next, highlight the numbers you want to divide by 1000.

Next, go to Edit - Paste Special (Alt, E, S) The click on divide ( press I), and select okay (press enter). (All together now, you press Alt, E, S, I, Enter...that's a LOT easier than moving your mouse all over!)

Your numbers will now be divided by 1000!

Oh look at that - That's not expected (2009 is divided by 1m!)...Lets look at what happened.

The first number divided by 1000 as expected. HOWEVER (THIS IS IMPORTANT). If you have a formula, it will NOT hardcode your formula, but simply add the /1000 to the end. So in my example, I took the first number divided by 1000, then each time, divided it by 1000 AGAIN. Be careful using this function when you're working with formulas and not values.

Make sure you keep an eye on what you are trying to modify so you don't get unexpected results.

Multiply, Add, and Subtract all work in the same manner. Play around with it and see what you can do.

(Tip: A great way to convert "text" numbers into real numbers is to multiply the text by 1 with this function. They will all instantly revert to their appropriate numbers.

Enjoy

My Latest Products

A quick interlude from the excel lessons. I've developed three new products for my shop this week.

The first is the Expense Tracker. This will allow you to input your shop expenses, and get some quick summary information about your spending over time.


Second, is the Retirement Calculator. I've been working on this one for quite some time as a personal project (I started it about a year ago). I am quite happy with it, and it's worked well to help me understand what I need to be saving in order to reach my retirement goals. I realized I should share this with my fellow Etsier's, so you'll be able to find it in my shop.


Finally, in the same spirit as the Complete Business Solution, I've decided to combine the Retirement Calculator and the Personal Budget at a discount - $11 for Both in the Personal Finance Solution

I assure you, you will be happy with your purchase!

Thanks for allowing that quick commercial, my next post will be Part II of the date series.

Working with Date values - Part I

This will be a three part series: The first part, to be covered today, will discuss ways to transform a specific date/time stamp value in a cell into data that you can use. The second part will be how to use those values to calculate differences in days or months, or any level of detail you want. The third value will explain how to change these into specific formats so you can display the data you want after you've calculated what you need.

Functions of the day:
Second
Minute
Hour
Day
Month
Year

Notice a pattern?

Two others we'll use
Date
Time

If you've ever tried to add or subtract certain amounts of time, it can get quite frustrating if you don't know how to use these formulas.

Lets look at my sample Spreadsheet:


You can see in Cell C2 I have a time, which just happens to be the time I created the time in that cell! (Nifty Trick - you can use ctrl + ; to input the date any cell, and ctrl + shift + ; to input the time. I then just changed the formatting to show you all the data)

the problem with a date/time value, is you can't just simply add or subtract from it, and have it make much sense. Instead, you need to know how to convert the values from date/time to numbers, and back.

Now lets say you wanted to turn these back into a date/time. This is where the date, and time, formulas come in.
=Date (Year,Month,Day)
=Time(Hour,Minute,Second)

so if we wanted to put my date back together, it'd be =DATE(C9,C8,C7)


Similarly, if you wanted to put the time back together, it'd be =TIME(C6,C5,C4)


We'll look at how to use these to our advantage more in the next post...

Using Formulas in Conditional Formatting

Today I'm going to look at how you can use conditional formatting to quickly give you visual cues about your data. I'm going to shamelessly plug the Product Analyst throughout this post to demonstrate how you can use conditional formatting to help you understand your data.

Lets start with our spreadsheet:


Ignoring the fact that I don't have enough sales to actually necessitate visual cues, lets say you were much more successful, and you wanted to know at a glance, what item you sold the most of (we can use this technique for many other things, but this is pretty straightforward).

Before we get to the conditional formatting, you'll need to be familiar with the large formula. This formula will give the xth largest number in an array you define (see prior post for definition of an array).
=Large(array,k) where k is the xth largest number

So, lets open up the conditional formatting screen:


By default, it's on "cell value is". This is great if you're trying to format your cell based on a value of another cell, but I can't use the formula I want here, so I'm going to switch to "formula is" with the drop down on the left.

Now, I'm going to use my "large" formula. When using the "formula is" option, the conditional formatting will occur if the formula returns the result true. Thus, if I wrote "1=1" in there, the conditional formatting would apply. If I were editing cell A1, and I said "A1=1", and the value in cell A1 was one, the conditional formatting would apply.

I'm going to conditionally format cell C7. In the conditional formatting, I'm going to write =C7=Large($C$7:$C$11,1). Lets look at this in more depth. I'm looking for the LARGEST value (as indicated by the 1), in this array. then I'm asking if the largest value in that array, is equal to the value in C7. If so, I want to use the formatting I specify. If not, I want to do nothing.

I am going to duplicate this for the 1st, 2nd, and 3rd positions (I can add conditional formatting).

Once you have done this in C7, you can copy and paste the formatting over the area you want to have look at this. Now, my number 1 selling item will be the darkest green, and will get progressively lighter until I get through my 3rd highest selling items. Note that items of the same sold amounts will be colored the same, if in the top 3.

When finished, it looks like this.

Swapping column labels between letters and numbers

This evening I thought I'd post a quick tip related to the column labels.

Some people prefer to work in excel with the columns numbered, instead of lettered. It is fairly simple to change this setting.

Go to Tools -> Options. Click on the tab "Formulas", and check the box that says "R1C1 Style".

Some things to know.
Your formulas will no longer use the letters either. A1 becomes R1C1. Also, if you are using cell references in a formula, you will not see "absolute" references by default. Instead, cells will be referred to by their relative position to the first cell..

So if in B1, I had the formula =A1, in R1C1 mode
R1C2's formula would be RC[-1]

The R with no number means the same row, and the C[-1] means one row to the right.

Enjoy

How many ways can you add cells togther? Part III

The final installment for this summation series...

Some pre-requisite concepts:

Array: An array is conceptually identical to a table. In excel, an array is identified as a range. For example, an array could be "A1:A10", or it could be "A1:C10". In the first array, it's single-column, multi-row, and in the second, it's multi-column, multi-row.

Boolean Statement: In excel, you can can write a formula that includes a Boolean statement. The cell result will be either True, or False (also, 1 or 0). For example. If I wrote in a cell ="Apple"="Apple" the result would be TRUE (or 1). If I wrote in a cell ="Apple"="Orange" the result would be FALSE (or 0).

Using these concepts with the sumproduct formula, you can accomplish any number of logical criteria:

Sumproduct: =sumproduct(array1,array2). This formula will multiply numbers in the same position in the arrays (#1 in array 1 * #1 in array 2) and then add up the results (#1's multiplied + #2's multiplied)

We're going to modify the standard use of this a bit for our purpose. Everything we do will be contained in the first position of the formula.

Lets get to some examples.

First, we'll do a standard sum =SUMPRODUCT(D4:D10) - this is pretty straightforward since there are no other pieces to this function.

Next, lets say we wanted to add up all of the Apples (Just like my last post, but instead of the sumif function, we'll use the sumproduct function)


=SUMPRODUCT(($B$4:$B$10="Apple")*D4:D10)

You can see the D4:D10 reference is still the same, I've just added an argument in the front.

First, we've got $B$4:$B$10). This will, within our formula, create an array that has the values (Apple,Apple,Orange,Apple,Banana,Orange,Kiwi) By adding the ="Apple" piece, I now use the Boolean test for each value:

(Apple=Apple, Apple=Apple, Orange=Apple,...,Kiwi=Apple)etc. This changes my array values to (true,true,false,...,false), or more importantly (1,1,0,...,0).

So now I have (1,1,0,...,0)*(D4:D10). Or, (1,1,0,...,0)*(4,1,3,...,3). Excel will then match these together, so you get (,1*4,1*1,0*3,...,0*3). This is simplified to (4,1,0,...,0), and then further to 12, our answer.

Again, this exercise could have been accomplished with the sumif formula, but the key here is that you can continue to add more criteria simply by including addtional Boolean statements within the function. The sumif formula is limited to a single criteria.

For example, lets say I wanted to add up all the Green Apples.

=SUMPRODUCT((B4:B10="Apple")*(C4:C10="Green")*D4:D10)

This concept has come in quite useful when trying to set up a summary of a dataset that can change over time, or which has come in far more detail than you desire.While it may not be necessary for a small dataset like in this example, when you start to look at dozens, and hundreds, of lines of data, it becomes important to be able to parse through your data.

How many ways can you add cells togther? Part II

Lets begin Part II of the sum series.

Today's lesson is to allow you to sum a range of cells based on criteria you specify. This is accomplished with the sumif function. This can be a very useful function once you get to know it.

The syntax for this formula are as follows: sumif(range,criteria,sum rang)

Range - This is the range that your criteria are listed in. This can be the same as the range you are summing, or not.

Criteria - this is the cell that contains your criteria.

Sum Range - This optional range is the range you want to sum, based on the whether the criteria, and your prior range, result in a "TRUE" result to the test.

This is very simple once we walk through an example. I will continue using the fruit we were using yesterday.



Here I've got my inventory of fruit on hand (Don't mind the brown apples, those are spoiled). Lets say I wanted to use the sumif function to sum all of the Apples on Hands.
My formula would be: =sumif(B4:B10,"Apple",D4:D10)


The range I am looking at is in column B. I want the answer in column B to match "Apple". I could replace the hard-typed "Apple" with a cell reference, and that cell could contain the word Apple. This would let me quickly select the fruit I wanted to sum. Finally, the D column is the area I want to sum.

Less practical for this example, but often useful; lets say I wanted to sum everything that was greater than 3 (I told you it wasn't practical here). Instead of using all 3 pieces of the formula, I can just have my formula set to: =sumif(D4:D10,">3") . Now I've got the criteria range, and the sum range, as the same, and I want to count everything that is greater than 3.


Unfortunately, sumif will only work using one set of criteria. If you want to use the same logic for more than one criteria, the formula gets a bit more complicated. This will be discussed in Part III of this series.

How Many Ways Can You Add Cells Together? Part I

This will be a three part series, covering different ways to add cells together.

Today: The good ol'e addition sign, and the sum formula.

Addition:

The most elementary of ways to add cells together, but the core of many spreadsheets, the + symbol. Quite simple, your formula is =B1 + B2. In my example, the formula is in B3.





Alternatively, you can use the sum formula to accomplish the same task. There are two manners in which you can use the sum fomula. You can reference individual cells, and ranges.



Sum formula Example 1: Individual Cells

In cell B3, I've typed =sum(B1,B2)





Sum Formula Example 2: Cell ranges

In cell B3, I've typed =sum(B1:B2)



Sum Formula Example 3: Individual Cells and Cell Ranges

In Cell B5, I've typed =sum(B1:B2,B4)



As you can see, there are lots of ways to accomplish the same task. Part II of this series will cover the sumif formula with one criteria, and part III of this series will cover how to accomplish the sumif formula with multiple critiera.

Creating Variable Dependent Dynamic Print Ranges

Today's topic is a bit more advanced, but will wrap up my print setup series. (Disclaimer: I did not create this technique nor most others I post, they are just bits of information I've picked up over the years and reformulated into solutions for my problems). Warning - this will get to be a bit lengthy...

I'm going to discuss how to create a dynamic print range that is dependent on values you are using in your worksheet.

What??

Yes, it's a somewhat obscure, and rarely necessary technique, but I've needed it before, and I think there are those out there that will find this useful.

Scenario: You're working on a workbook that is designed to calculate fixed-rate mortage payments, and you've got an amortization table you would like to be able to print out. Unless you are using a macro to fill your formulas to the appropriate row based on the repayment terms, chances are your technique is to take the formulas, copy them down a couple hundred rows, and then just ignore the cells that aren't used. The problem with this is if you need to print the workbook, you will either print a lot of uncessary white space, or you will need to define your print area using the manual print area setup. This is not really practical if you are running numerous scenarios often. Instead, with my technique, depending on your term, you will be able to have the print range set itself to exactly where you need it.

Solution: We are going to use two features of excel to set your workbook up for this. We will define a named range, and we will use the offset function. I'll describe each individually, then show how we can use this for our purposes:

Define Named Range:

One of the nice features in excel is the ability to assign a name to a specific range. This range can be a single cell, or an area of cells. You could name a cell "TaxRate", or you could name an series of cells "TaxRateTable" all at once. Then, you can use these named ranges in formulas, as opposed to using the standard cell reference. For Example, if cell A1 were named "Dollars", you could write a formula in one of two ways:

A1 + B1 = C1 or

Dollars + B1 = C1

To name a range, go to Insert -> Name -> Define

You can type the name of your range in the top section, and then select your range in the bottom. click in the bottom box, and then you can click and drag on the spreadsheet to fill in the range. Alternatively, you can type the range in. Then press add, and it will be added to the list, and part of your workbook until you delete it.

Once you've named a range - you can always reference that range in a formula, and it will refer to the cells you've selected.

Print Area: When you set the print area (see prior post), you will notice that a named range is created called Print_Area. This is how excel keeps track of the area you've set, and so it knows what to print.

Offset Formula:

The offset formula allows you to reference a cell, starting from one cell, and telling it how many rows and columns to move away.

The formula is =offset(Reference, rows, columns)

Reference refers to the starting location. For example, A1. Rows refers to how many rows to move down (- moves up), and Columns refers to how many columns to move right (- moves left).

The result of the formula can be either the value of the cell, or the actual "range" of the cell, depending on the manner in which it is used.

Combining these techniques to define a dynamic Print Area:

You will notice when you define a named range, the box you define the range uses a formula. In this case, using the offset formula will allow us to refer to the range the offset formula gives us.

In this example, assume that the number of rows DOWN you want the print area to be is equal to the number in cell B1.

Print_Area =$A$1:Offset($A$1,$B$1,0)

So, the starting point in offset is A1, we are going to move down the number in B1, and we will move across 0 rows. If the number 10 is in B1, The print area will be set to A1:A11 (A1 + 10).

Here's the formula in the define named range box:

(You will notice it adds your sheet name automatically). If you go to print preview, you will notice the first 11 rows are included. If you change the value in B1, the print range will change.

Back to the mortgage example: I set my offset starting range to the 1st period in my payment term schedule. Then for the offset rows, I link it to the payment terms. Thus, if I have a 360 month mortgage, the print area extends 360 rows. If I have a 180 month mortgage, the print area extends 180 rows.

From the Hotel - Page Setup - Multiple Ranges

Good Evening,

I figured I'd spend a couple of minutes this evening to post a quick item related to this weeks topic. By the way - I highly recommend Embassy Suites to anyone traveling.

Today I'm going to keep it brief, but I thought I'd touch on another more advanced method for setting print area.

Click on File-> Page setup, and look at the print area. You will notice that your range is over a single area. In my Example, it's A1:Y7.

If you have a spreadsheet where you want to print two pages, on two areas, but don't want anything else, you can create a custom print area to highlight these two areas. To do this, click one in the print area box. Then click and drag an area on your spreadsheet. Then hold down the ctrl key, and click and drag a second area on your spreadsheet. The resulting range in the print area should look like below.

Now, using the Page Break View discussed yesterday, you can see that two areas are surrounded by the blue box. Now that you have two areas, you can continue to change the range of the print area by clicking and dragging the blue box.

Good Luck

The First Post, the First of Many Excel Lessons

Thank you for stopping by. I hope you find the information here useful. Feel free to comment, or request future lessons. My goal is to have these lessons work as a progression. To begin I will start some some basic features of Excel. As this blog progresses, I will begin to work with more advanced features of Excel, and will begin doing series of posts for more complex topics. Please feel free to suggest topics if you have something you know you should be able to, but just aren't sure how.

This Weeks Theme: Printer-Friendly Spreadsheets
Todays Lesson: Basic Methods of Setting the Print Area

Often I am sent spreadsheets from friends and co-workers, which when printed, output very poorly. This is often the fault of little to no attention to the print area of a spreadsheet. Today, I am going to cover three ways in which you can set the print area.

• Set Print Area
• Page Break Preview
• Page Setup
  

Set Print Area
The first option is to highlight the area you want to have print with your cursor, and then use the Set Print Area Option.

First, highlight the cells you want to show up when you print. In my case, I want Range A1:I20 to print:


Once you've done this, go to File -> Print Area -> Set Print Area:


Now, when print, only the selected range will print out. This is great if you have work on the side of your worksheet that you don't want to print.

Page Break Preview
Page Break Preview allows you to view and edit the printable area of your spreadsheet as you are editing it. To enter Page Break Preview, click on View -> Page Break Preview.


You will notice that the area on the inside of the blue box has a white background, and everything outside is grey.


The area that is grey will not print, while the area that is white will print. You may modify the print area of your spreadsheet as you are working by clicking and dragging the blue lines in any direction.

Page Setup
Page setup allows you to define the number of pages you want your particular spreadsheet to print on. You can choose to either set a print area with one of the two above methods or not prior to doing this. Regardless of whether you do the above, you will be able to limit the number of pages your document pages on. The result is a potential shrinking of your text so that it will all fit on one page. To edit Page Setup, go to File -> Page Setup


Once in this screen, you can either click on "Adjust to % of normal size" or Fit to x by x pages. I recommend using the second option.


In our example, I've selected to fit the page to 1 wide by 1 tall. This means that everything on my spreadsheet will print out on only one page. Be careful, because if you have too much data on one page, this would result in some very small font!

As a final check to make sure your worksheet is going to print as you expect, I always recommend using the print preview feature. This allows you to see how your computer will print the document before you waste your paper on something that is completely unexpected.