Sunday, September 14, 2008

Can you afford to be in business - Part 1

Questions pop up here and there of people discussing their financial issues, with either Etsy, personally, or some mixture of the two.

I’ve decided to write a series of articles to help you answer the question –“ Can you afford to be in business?”. This is the first article in the series, “Understand your Personal Finances”.

Why should You care?

Before you can even begin to answer the question “Can you afford to be in business?”, you need to have a good basic understand of your personal financial situation. This may come as a surprise to some, but if you have taken the leap to running an Etsy business (or any small business), and you don’t know whether your personal budget works, you may have mistakenly made a bad decision.

Consider this: Most successful businesses somewhere in the ballpark of a 5-25% profit margin. If you have credit card debt, and you’re paying 15-20% a month in interest on that balance, you’d be improving your financial position more by paying that debt off, and having no business at all, then trying to run a business, and earning a margin on what will ultimately be a small amount of revenue.

There is definitely some value to the intrinsic “running a business makes me feel good”, I will not deny that, but the trick is to make sure you aren’t digging yourself deeper with what is ultimately, another form of self gratification.

So how do You get started?

The first thing you need to do is understand how much money you bring in, and how much money you send out, every month. This may seem like a daunting task if you have not really tried this before, but we’ll walk step by step through how you can approach this.

Gather the necessary Documents

The first thing you need to do is collect all of the documents you have that display your spending each month. The easiest way to do this is to collect your bank statements, and your credit card bills. If you spend a lot of cash, there is no way to go back and analyze this information, but you can begin to write down what you spend your money on, as you spend the cash. ( I recommend avoiding using cash as much as possible for just this reason.)

Categorize into Income and Expenses

Now that you’ve gathered these documents, you need to understand what each line on the statement is for. It may seem like a lot of work at first, but the payoff will be tremendous, so stick with me.

I recommend you use a spreadsheet to keep track of your information. If you don’t have one, you can easily download a free program at http://www.openOffice.org.

For each item you have on your bank statement and credit card, write down the following information in a spreadsheet:

Date

Payee / Payer

Income / Expense

Category

Date is simply the date of the transaction. Payee/Payer is either who paid you, or who you paid. Income or expense just lets you know whether you were spending money, or getting money Category is the crucial one, and we’ll explore that in more detail here.

Potential Categories

You should not be too detailed with your categories, but not too vague either. I recommend you use categories such as Food – Dining Out, Food – Groceries, Utilities – Electricity, Utilities – Gas, Rent, Insurance – Car, Insurance – Home, etc. We will need this level of detail in the next article to help understand where your money is going, and what you can do to cut back on your expenses if necessary.

Once you have finished recording all of this information, take a break! You don’t need to do it all at once. Perhaps do one month a day for 3 days. Maybe half a month a day for 6 days? Don’t overwhelm yourself, but make sure you give it the time it deserves.

That is all for this article. In the next article we will explore the data you have collected, and gain a better understanding of your personal budget, and how you can take steps to get control over your personal spending, if necessary.


Wednesday, September 3, 2008

Are you Canadian? Do you craft? Perhaps both?

I stumbled across this website this morning, which I thought was quite interesting. It's the Canadian Women's Business Network. They have a special section dedicated to different resources you might need to set up your business, as well as various articles about some interesting topics. Things like "Selling your one of a kind items to galleries" and "The Interactive Business Planner".

Check it out! http://www.cdnbiz.net/crafters/craft_business_basics.html

Monday, August 25, 2008

New Product: Holiday Shopping Budget

A small interlude from the standard Excel tips. I just put out a new product on Etsy today, and I think it's a great one for the holidays! It's a quick, and easy to use Holiday Budget & Shopping List.


How does it work? Once you've purchased it, you'll get it in your inbox within about 24 hours. Upon receipt, you'll see a place to add up to 8 people to shop for, with 6 gifts each (need more? Send me an email and I'd be happy to customize it). Fill in the gifts, and how much you want to spend, and you'll quickly see what you can expect to be your total Holiday Spending. Then as you shop, you can come back and fill in the amount you actually spent, so you can see how much you have left for little Joey in case you went over on Grandpa's budget.


Saturday, August 23, 2008

Create a formula to always display today's date

Developing invoices? Just want to keep track of when you printed a page out? There are many reasons one could need today's Date, Time, or some variation to always be present on the spreadsheet.

Today we'll cover how to do that, using the formula =now()

If you type the formula =now() into a blank cell, it will display just that. It will show you the date, and the time.

You can modify this in many different ways. If you edit the cells formatting preferences (right click -> format -> number tab) you can make the date and time look however you want.

Another trick is to use the =text() function. This function allows you to convert any number into text of your choosing (date/time is a number in excel, you just typically don't see it that way)

the format is =text(dateTime,formatStyle).

for dateTime, I'd just put in the formula =now(), so it looks like:

=text(now(),formatStyle)

To see your different options for "format style' I recommend right clicking on a cell, clicking "format cell", going to "number" and selecting the custom type. You will see lots of formatting codes (and can test them right in the window) to figure out what you need. Once you've figured out the code you want, replace formatStyle with it.

=text(now(),"MMYYDDD") would make that cell today show "08232008". If I put in:
=text(now(),"MMMM") it would just show "August".

I think this function can have lots of uses, so have at it!

Monday, July 21, 2008

Edit Comments Quickly - No mouse Required!

Hello all,

A quick tip here tonight I thought I'd share with you.

In case you didn't know - you can right click on a cell, and type "Insert Comment" and you will get a little post-it looking box that you can type in to leave notes for yourself or others about the cell.

If you're like me, and you hate using the mouse for one reason or another, you can create a new comment, or edit the existing comment, simply by pressing shift + F2. Viola!

Getting out is just as easy - just hit the escape key twice. It may seem a little odd at first, but once you get used to it, you'll be navigating the comments like never before!

Friday, July 18, 2008

An Update - Aggregate Sales


I've received aggregate sales from a few other Etsier's

check out the results thus far!

Care to contribute to the Etsy community sales data to find out what day the most sales occur? Send me your data.

Click here to find out how!

Tuesday, July 15, 2008

Congratulations Etsy - Some Statistics

I thought I would post some statistics about the success Etsy has been having. I will update this later with some more detailed data tables, for those that really love the numbers.

Feel free to check out the original thread with this information.

http://www.etsy.com/forums_thread.php?thread_id=5713729

If I had to describe Etsy's past 6 months, I would say.

Amazing

Why?

Thanks to a very open staff, they've left us with some key financial and shop data points month over month since January. I thought I'd take a closer look, and share some progress Etsy has made, to help you realize what a storefront we've got!

(Warning: This is a long thread, but I wanted to share as much useful information as I could!)

Total Sales:
January 2008: $3.8 Million
June 2008: $6.5 Million

That represents a 71% increase in 5 months. That is amazing.

Items sold:
January 2008: 273043
June 2008: 442000

That represents a 62% increase in 5 months. One interesting note about this, as sales have increased faster than items sold, that means the average Etsy purchase has increased in dollar value. Just how much?

Average Price per Sale:
January 2008: $13.92
June 2008: $14.71

Without a distribution of the actual sales, one can't begin to guess what the ideal price is for the typical Etsy buyer, but this is an interesting place to look to compare your current shops average prices.

Items Listed:
January 2008: 575174
June 2008: 742000

A 29% increase over the past 5 months.

Ratio of Items Listed to Items Sold:
January 2008: 2.1
June 2008: 1.7

This number decreasing is a good thing, meaning that sales have started to gain on new listings. A perfect 1:1 ratio would mean an item sold for every item posted!

New Members:
January 2008: 68000
June 2008: 80000

An 18% increase. Unfortunately, the member statistics are a little less fuzzy, as it appears Etsy switched their reporting in May. Prior to may the reported on new buyers, whereas in May and June they provided buyers and sellers. If an Etsy staff member wanted to comment on this to clarify, I'm sure the contribution would be appreciated! If not, I'm sure we're all more than grateful for the information you've provided.

As I said at the beginning, Amazing. Of all the numbers, the only place any hiccup is seen in the growth over the year was in items sold, which decreased 1.5% from May to June, and Total sales, which held flat (despite the decrease in items sold!).

So to you Etsy, and to those who've made Etsy what it is, I say Thank You.

Friday, July 11, 2008

How to create buttons to move between sheets

Today's tip is going to focus on a simple Macro and button that should help you design your larger sheets to be easier to move around for yourself and others.

My Sample large sheet has 5 whole tabs.


We're going to use the control toolbox for this one (right click in the blank space up at the top near the help box, and enable control toolbox





Once the control toolbox comes up, we're going to use the Command Button





Click on the button, and then click in your spreadsheet



Right click, and select properties. Your interested in two specific options at this point -> Caption, and (name).



(name) is what excel calls the button - caption is what you and your users see on the button. I'm going to create 4 buttons on the summary Tab. One for each sheet. I am going to name them salesButton, viewsButton, daysButton, and listingButton. The captions will be Sales, Views, Days, and Listings
Once you've set up the buttons, while still in design mode (the little right angle ruler clicked)
double click on your button. This will bring up the visual basic screen. You'll see a lot of things you don't need at this point. All you are concerned with is the main window. For the Sales button, you'll see the following:


The code to move between sheets is very straightforward. Sheets("Sheetname").select

So to select the Sales sheet, we'll input sheets("Sales").select

That's all you need! Now when you turn off design mode (uncheck the design button) and click on the button, it will select the Sales sheet! This may seem pretty mundane for my 5 spreadsheet book, but when you start having dozens of sheets, creating a few buttons on the top or bottom of each sheet for your users to get back to where they came from, or where they need to be going, will make this a LOT easier to use. Just make sure you advise people not to change the sheet names on you, or things will stop working.

Sunday, June 22, 2008

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

Friday, June 20, 2008

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

Wednesday, June 18, 2008

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.

Tuesday, June 17, 2008

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...

Saturday, June 14, 2008

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.

Friday, June 13, 2008

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

Thursday, June 12, 2008

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.

Tuesday, June 10, 2008

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.

Monday, June 9, 2008

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.

Sunday, June 8, 2008

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.

Saturday, June 7, 2008

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




Friday, June 6, 2008

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.