Archive for the 'Excel' Category

Random Numbers in Excel

Published by Tiposaurus on September 25, 2012 in Excel. 0 Comments Tags: Excel, excel 2007, excel 2010, rand, randbetween, random number.

To get a random number in Excel, use the RAND() function, which returns a random number between 0 and 1. It will return a different random number each time the cell is recalculated, such as when you reopen the workbook (e.g. press F9 to refresh and you'll see the value change).

Random Numbers In a Specified Range

To get a random integer (i.e. whole number) in a particular interval, use the RANDBETWEEN() function, which has two inputs noting the lowest and highest values you want random numbers between. E.g. =RANDBETWEEN(200,300) will return a random integer between 200 and 300. Again you'll get a new random number each time you refresh (you can Copy and Paste Values if you want to keep the random numbers you've generated).

If you need a random number in an interval which isn't rounded to the nearest whole number, you can use the RAND() function output to do get this. For example, =200 + RAND() * (300-200) will give you a random number between 200 and 300 which isn't rounded.

The output from each of these functions is shown in the below output:

Get today's date in Excel

Published by Tiposaurus on September 19, 2012 in Excel. 0 Comments Tags: date, datetime, Excel, Excel 2003, excel 2007, excel 2010, shortcut, time.

Working with dates can be a key thing in Excel. This tutorial gives you a few different ways to get the current date in Excel 2003, Excel 2007 and Excel 2010.

The NOW() function

One way to get the date is using the function =NOW() which just puts the current date and time in to the cell as follows:

You can then format the cell (right click -> Format Cell, or Press Ctrl+1) to display it as you like, for example as a date in the format dd/mm/yyyy. Continue reading 'Get today's date in Excel'

How to colour cell borders in Excel

Published by Tiposaurus on September 19, 2012 in Excel. 0 Comments Tags: border, color, colour, excel 2007, excel 2010, spreadsheet.

This is quite a simple tutorial, but can help make your Excel 2007 or Excel 2010 spreadsheets more attractive if used sparingly. You can set the colours of cell borders by following these steps.

Select the cell you want to colour in.
Continue reading 'How to colour cell borders in Excel'

Shortcut key to paste values in Excel 2010

Published by Tiposaurus on August 27, 2012 in Excel. 1 Comment Tags: Excel, excel 2010, paste, paste values, shortcut.

If you came here looking for an easy short-cut key to paste values in Excel then it's bad news I'm afraid - there just isn't a simple keyboard shortcut in the same way there is to cut, copy or paste normally. However there is a way to paste values using the keyboard, which this tip shares with you.

Alt Keys

If you press Alt in Excel, you may have noticed that Excel puts letters on the ribbon, indicating the Alt key associated with that tab or function. If you press Alt, then press the letter for the tab you want (e.g. H is the home tab), you can then choose the function you want from that tab (e.g. C for copy, V for paste), and so on. Some items, including paste, have further sub-items which you can select by stringing these letters together.

So to get to paste values this way, you can do the following0: Alt - H - V - V. Just press each one in time, no need to keep any key pressed.

How to password protect your spreadsheets in Excel 2007 or Excel 2010.

Published by Tiposaurus on August 16, 2012 in Excel. 0 Comments Tags: excel 2007, excel 2010, password, protect, tutorial.

The option to require a password to open an Excel spreadsheet may be in a different place than you are used to in earlier versions of Excel. Follow the steps below to require a password to protect your spreadsheet.

Choose File -> Save As.

Continue reading 'How to password protect your spreadsheets in Excel 2007 or Excel 2010.'

Temperature Conversion in Excel 2007 and Excel 2010

Published by Tiposaurus on August 16, 2012 in Excel. 0 Comments Tags: Excel, excel 2007, excel 2010, formula, temperature conversion.

Excel 2007 and Excel 2010 make it easy to convert between different units, such as temperatures using the CONVERT() function.

It is used as follows: =CONVERT(number, from_unit, to_unit).

Continue reading 'Temperature Conversion in Excel 2007 and Excel 2010'

How to set 'Read Only Recommended' in Excel 2007 or Excel 2010.

Published by Tiposaurus on August 14, 2012 in Excel. 2 Comments Tags: Excel, excel 2007, excel 2010, read only prompt, read only recommended.

The option to give people a prompt that open an Excel spreadsheet recommending them to open it as 'read only' is in a slightly different place in Excel 2007 and Excel 2010 than it was in earlier versions. Follow the steps below to find it.

Choose File -> Save As.

Continue reading 'How to set 'Read Only Recommended' in Excel 2007 or Excel 2010.'

Excel Tip: Evaluate part of a formula directly in the formula bar

Published by Tiposaurus on May 27, 2011 in Excel. 0 Comments

Quite an unassuming tip today, but potentially useful (and not immediately obvious).

In Excel, if you have a complex formula within a cell, you can immediately evaulate part of it by selecting the part (dragging across it with the mouse or by using arrow keys), then press F9.

For example, if you have:

=cos(3+2/5)

then you select the 3+2/5 part:

=cos(3+2/5)

and press F9:

=cos(3.4)

This is useful for simplifying formulae, and also diagnosis when something isn't working as expected. Note that if you press enter (or click on another cell), it will save the formula with the evaluated number in the cell, rather than the original one. To avoid saving it, press Escape when you're done editing.

So for the above example, enter saves the formula =cos(3.4) in the cell, while pressing escape returns it to =cos(3+2/5).

How To: Get the Weekday as Text in Excel

Published by Tiposaurus on June 10, 2009 in Excel and VBA. 9 Comments Tags: date, Excel, Excel 2003, excel 2007, excel 2010, VBA, weekday.

I often want to know what day of the week a date is. Excel has a function for this, but unfortunately it only returns an integer, for example the function:
=WEEKDAY("01/02/2009")
returns 1 (which represents Sunday).

This can be a bit confusing with a list of dates, so I wrote the following quick VBA function to get the day of the week as text, e.g. Sunday, Monday, etc.

The function (possibly not the most elegant method possible) is:

' function to return a text representation of the weekday of a given date ' returns the full text, which can be shortened by left(), etc Function dayText(d As Date) As String If Weekday(d) = 1 Then dayText = "Sunday" If Weekday(d) = 2 Then dayText = "Monday" If Weekday(d) = 3 Then dayText = "Tuesday" If Weekday(d) = 4 Then dayText = "Wednesday" If Weekday(d) = 5 Then dayText = "Thursday" If Weekday(d) = 6 Then dayText = "Friday" If Weekday(d) = 7 Then dayText = "Saturday" End Function

(Enter this via the VBA Editor - press Alt+F11, then insert -> module and paste this code in).

It can then be called by:
=DAYTEXT("01/02/2009")
which returns Sunday.

This can then be shortened to one or three letters via the left() function, for example:
=LEFT(DAYTEXT("01/02/2009"),3)
returns Sun.

If you want to avoid macros, an alternative method is to use the (slightly messy) formula consisting of nested IFs, for a spreadsheet with a date in cell A1:
=IF(WEEKDAY(A1)=1, "Sunday", IF(WEEKDAY(A1)=2,"Monday",IF(WEEKDAY(A1)=3, "Tuesday", IF(WEEKDAY(A1)=4, "Wednesday", IF(WEEKDAY(A1)=5, "Thursday", IF(WEEKDAY(A1)=6, "Friday", "Saturday"))))))