August 28, 2004

Sunken & Raised Text

Here is a way to get sunken and raised text.

First you will need your Drawing toolbar. If it's not already displayed, right click any other toolbar and select it from the menu.



Then using WordArt, choose your style and edit the text as necessary.



Next you will need to choose your Shadow Style. Use Style 17 for raised text and Style 18 for sunken text.



This is what you should end up with. (I prefer making the text and background the same colour)



It's a good effect to use for things like Hyperlink buttons.  

Posted by andrewe at 16:36

August 25, 2004

Sum Labels

You can define ranges and formulas as Names from the Insert Menu. (Here's a link to a previous post)

You can also use Labels to define a range in a table as shown in this picture. (Besides SUM, you can also use certain other functions such as INDEX)



Here are some advantages.

1. After entering a formula into a single cell, you can also drag it into neighboring cells.

2. When writing the formula, you can use either upper case or lower case.

3. If you change the Label name, the formula will adjust automatically.

Note: Be careful that you don't include the totals as part of yet another formula. (You might want to move the totals away from the table to prevent this)

To make it work, make sure that Accept labels in formulas from Tools, Options, Calculation is checked.



And now, back to watching the Olympics, see you next time!  
Posted by andrewe at 23:17

August 23, 2004

Show Extra Rows 2

It's been mentioned on both my English and Japanese blogs that simplifying formulas by using more cells can be a good thing. Apart from being easier to enter and spot or prevent errors, it can also be more flexible if certain criteria need to be changed.

In my previous post, I used a logical formula to return either TRUE or FALSE in one column, and OFFSET in another columns to select rows. (By "logical", I don't mean the WEEKDAY function, I refer to returning TRUE or FALSE, I'm talking about the formula, not the function name)

Anyway, my logical formula was straightforward enough but the OFFSET formula to select rows was a bit sloppy, so I thought about it a while and came up with this alternative.

Instead of TRUE or FALSE, I decided to use 1 or 0. How do you make TRUE into 1 and FALSE into 0? Just add some brackets and 2 minus marks as in =--(WEEKDAY(B5,2)=$C$2).



Then in the next column, I used a tidier version of OFFSET combined with SUM as in =SUM(OFFSET(C5,-1,0,3,1)).



So how does it all work?

I'm using SUM to count the 1's in the range provided by the OFFSET function.

OFFSET(reference,rows,cols,height,width)

In the case of =SUM(OFFSET(C5,-1,0,3,1)), C5 is the reference, and you can increase rows on either side by adjusting rows by multiples of 1 and height by multiples of 2.

And the double minus marks? Putting it simply, the first minus mark changes TRUE or FALSE to their numerical value but as a negative, the second minus mark changes the numerical value back to positive.

Please let me know if you have any interesting alternatives.  
Posted by andrewe at 00:56

August 18, 2004

Show Extra Rows

Here's an example where a couple of helper columns might be useful.

Let's say when I choose a weekday with a filter, I still want to refer to the day before and day after. So if I select all Wednesdays, I should also be able to see every Tuesday and Thursday.

I'll start with the WEEKDAY function. I just want TRUE or FALSE, so my formula is a variation of =WEEKDAY(B5,2)=$C$2 where cell C2 has a value of 3, and I drag down the remaining cells in Column B as necessary.



If the dates in Column B are Wednesdays, the above formula will return TRUE, or FALSE if the date falls on other weekdays not Wednesday.

Now in Column D I add this formula.

=IF(OR(C5=TRUE,OFFSET(C5,1,0)=TRUE,OFFSET(C5,-1,0)=TRUE),1,"")

With a filter, I can now select "1" to show Wednesdays, plus an extra row above and below.



A bit ungainly, but if you use something like this at work everyday, it might be well worth the time invested to set it up.  
Posted by andrewe at 22:49

August 16, 2004

3rd Largest

Using a helper column can sometimes make things a lot easier.

It's not too hard finding the maximum or minimum etc if working with criteria. This is an example of a MAXIF formula (Note: Don't enter the brace brackets. Push Ctrl, Shift and Enter to make it an array formula, they will appear automatically)

{=MAX(IF(A1:A100=A1,B1:B100,""))}

You'll notice the double quotation marks at the end, they are there to prevent problems if dealing with zeroes and negative numbers, otherwise {=MAX(IF(A:A=A1,B:B))} should be okay.

How about if I wanted the 3rd largest value based on a criteria? Here's one way to do it using a helper column.



In cells C6:C30, I have a list of names and in cells D6:D30, I have a list of scores. In cell E6, I enter =IF(C6=$C$4,D6,"") and drag down.

Cell C4 is the cell I use to enter the criteria, in this case a name. In cell D4, I enter 3 which is the rank (3rd highest score).

In cell E4, I enter the final formula.

=LARGE(E6:E30,D4)

This looks up the 3rd highest value (or 3rd largest value) in cells E6:E30.

Could I do all of this without a helper column? Yes, here is a formula that will do the job. (Don't forget to use Ctrl, Shift and Enter)

{=LARGE(IF(C6:C30=C4,D6:D30),D4)}

So why use a helper column if I can just use one formula? Helper columns can offer you a lot of flexibility and have certain advantages over just using one single formula. It's a good idea to get used to using both.  
Posted by andrewe at 18:50

August 13, 2004

Being Trendy

Rather than use Fill formats, here are a couple of ways to do the same thing using functions.

The TREND Function



The picture above shows sales figures from January to June. The TREND function as been used to predict sales figures for July to December by using my mouse to select the corresponding cells, then pushing Ctrl + Enter at the same time.

Let's look at the formula. =TREND(known y's, known x's, new x's) where known y's are sales figures from January to June, known x's are months January to June (1 to 6), and new x's are months July to December (7 to 12)

The FORECAST Function

This works in a very similar way to the TREND function.



=FORECAST(x, known y's, known x's) where x is the month that you want to forecast,in this case July, known y's are sales figures from January to June, and known x's are months January to June (1 to 6).

Advantages

Both TREND and FORECAST have the advantage of being "permanent" fixtures to your spreadsheet whereas Fill from the Edit menu must be updated each time.

TREND is easy to work with a range of cells by using Ctrl + Enter. FORECAST can be improvised to work with a range by using absolute references and then dragging to select the remainder of the range.  
Posted by andrewe at 13:28

August 10, 2004

Fill 'er Up Part 2

Here are a few other things you can use to fill cells.

The Fill Format
Enter any number or text in a cell, then right click to select Format Cells, Alignment, then use the drop down list shown as Horizontal to select Fill.



Anything you enter will repeat to fill the entire cell.

The REPT function
If you just want to repeat something a certain number of times, you can use the REPT function as in the picture below.



A Custom Format
This is a way to mix entries with whatever Fill format you choose.

Right click the cell to select Format Cells, Number, Custom.

If you wanted a line of dots to show continuously (fill) to the right of your entry, use @*. or you can use *.@ to show dots to the left.



Experiment and have fun!  
Posted by andrewe at 22:30

August 08, 2004

Fill 'er Up Part 1

Excel has lots of useful Fill features. Here are just a few of them.

Edit, Fill
Here's a look at the Fill options available from the Edit menu.



Using Down, Right, Up and Left, you can quickly copy data, format or formulas into neighboring cells using the direction you choose. (Take note of the Ctrl + D and Ctrl + R shortcuts. Another useful shortcut is Ctrl + Enter which can be used to enter the same data or formula into multiple cells simultaneously)

You can also copy across Worksheets. Right click the Sheet tab to access Select All Sheets, then use Across Worksheets from the Edit, Fill menu.



Series
There are some very good options available using the Series dialog box (Edit, Fill). Here are some examples.

Here I have used Linear which can be used to add the number entered in Step (in this case 10). I've also used Stop to for a maximum value of 50 (if I didn't use Stop, the series would continue to end of the range selected with the mouse)



In this case I have used Growth to multiply by 10. Again, the Step value is 10 so all cells that follow are in multiples of 10.



Here I have added months by using Date, Month with a Step Value of 3.



Finally , we have the Autofill option. To be honest, I think it's a lot easier to use the Auto Fill handle and it offers more options also, depending on what you actually want to do.

In the above picture I used a Step value of 3 to add 3 months at a time. This is also possible with Autofill, but you have to "specify" a step value by entering the first few cells to get the same step value throughout the range.

At the same time, if your are dealing with a step value of 1, then the Auto Fill handle may be the way to go.



Custom Lists
You can also add make your own Custom lists by using Tools, Options, Custom Lists, NEW LIST. If you want to use the same data in the same order on a repeated basis, this is a very handy feature. Note that the list can be used continuously once entered, unless you choose to delete it later on.



Once your Custom List is set, you can just enter the first cell, then drag down to fill following cells with the entire list.  
Posted by andrewe at 20:37

August 05, 2004

Excel Snake

My good buddy Colo (aka Masaru Kaji, Excel MVP) has outdone himself in making his new Excel Snake game. Try it out, it's a lot of fun! You can download the game here.


While you are there, please browse around the rest of the site. There's tutorials, downloads, and much, much more. It's great for newbies and experts alike.

Go to Colo's Excel Junk Room.  
Posted by andrewe at 23:11

August 04, 2004

Superscript and Subscript

One thing I always found hard was how to shrink characters such as used with equations, measurements or chemical compounds. For example, how can I show cubic meters as a number?

It's quite simple when you use Superscript and Subscript. Enter the cursor into the cell and drag across the character(s) you want to select. Then right click and select Format Cells.



In the Effects section, check Superscript or Subscript as you prefer.



Here's a few of things you can do.



Have a look at the third one down. To get a degree symbol, I took an easy way out and used a small "o" (opposite of a capital "O").

Unfortunately Superscript and Sunscript don't work well with formulas. In this case, you can try inserting a degree symbol (Insert, Symbol for recent Excel versions) and then using a formula such as =A1&"°". You can also try using the CHAR function (Please note that some symbols appear to vary according which language version of Excel you use)

Superscript and Sunscript doesn't seem to work with numbers only either (all text, or text combined with numbers is fine). So how did I get the fourth one down?

I was sneaky. I formatted the cell as text first. :-)  
Posted by andrewe at 22:23

August 01, 2004

Download My Tips

Over the last couple of weeks, I have been putting together a bunch of tips that can be downloaded from my site.

Help Me to Help You
A lot of these tips are my own, but some are tips I have come across elsewhere.

If you have any tips that you would like to share, please email me and I will include them in future updates. (subject to approval)

Also, credit will be given where it can be proven. If you want credit, please provide some proof that you are the originator.

Here's the Tips file. Please note that it uses Treepad software (supplied with the tips).  
Posted by andrewe at 19:06