Posted on Leave a comment

How to generate random numbers in Excel

Here is how you can generate random numbers in Excel:

Put all your possible characters into an individual cell, such as A1:

You can name this cell MySource. You could then use a formula such as the following to return the random string of characters (broken into individual lines for clarity, but it is still a single formula):

This long formula concatenates eight characters pulled from the cell source entered in A1

There is another approach for this that uses a table that contains all the characters you want in your random text string. Start by placing the number 1 through 62 in a column, one number in each row. To the left of these numbers place your other charactheres – A, B, C, D, E etc. (this should be the same characters you placed in cell A1, using the previous method).

Select both columns of the 63 rows and name it MyTable. You can then use the following formula to generate the random characters (broken into individual lines for clarity, but it is still a single formula)

 

Attention: Both approaches have one drawback – they are regenerated each time the worksheet is calculated making it impossible to have a single generated random string that will not change on a regular basis. The best way around this is to use a macro that will put the random strings into your workbook starting at a specific cell location

Run the macro, and whatever is in cells D4:D53 is overwritten by the random values. If you want the values written into a different location, change the Range statement near the beginning of the macro.

If you want to try it you can get it from my Skydrive: Download Random Numbers Generator

Posted on Leave a comment

Count Cells with Numbers

Excel Count Formula

If you need to count all cells with numbers you can use the Excel COUNT function which counts cells that contain numbers. Its syntax is:
=COUNT(value1, value2,…valueX).
value1, value2,…, valueX  can be cell references, or values typed into the COUNT formula.

Excel COUNT function example – count numbers in cells A2:A7.

  1. In the cell where you want to count number enter the following formula =COUNT (A2:A7)
  2. Press Enter to complete the formula

Excel Count Formula

The result will be 3, the number of cells that contain numbers.

Excel Count Formula Result

Cells A2, A4, and A6 aren’t counted, because they contains text

Note: Dates are stored as number, so the COUNT function will include any cells that contain dates.

Posted on Leave a comment

Find duplicate cells values in Excel 2010 & 2013

Duplicated Values result

To quickly find duplicate values in a Excel spreadsheet you can use Conditional Formatting under Home tab. There is a special rule for this called Duplicate Values…

Highlight the cells in which you want to find the duplicates and click on the Conditional Formatting | Highlight Cells Rules | Duplicate Values

Excel Duplicate Values

Select the formatting for duplicates and click OK.

Duplicated Values result

Posted on 2 Comments

Create a pie chart from distinct values from a single column

The Country column represents the data for which you want to create the pie chart (with percentage for each country). You need to add a column and fill it with 1 (this will be used to count how many times each country appears in my Country column).

1

Select the data from both columns and create a Pivot Table: go to Insert tab and click on the Pivot Table | PivotTable button. You have the option to create the pivot table in the same worksheet or on a new sheet)

2

On the PivotTable Field List drag Country to Row Labels and Count to Values.

3

Now select the pivot table data create the pie chart:

4

Using pivot tables allows you to update the data in the pie chart very easily – all you need is to update the Country column.

Posted on 1 Comment

How to change the default hyperlink font face/size in Excel

Microsoft Excel

I was having a problem with the cells containing hyperlinks –  the font was changing automatically into the default one (even though I have selected all cells and applied a different font).
In order to configure Excel to use the same font face/size as the one you selected for the entire sheet (or group of cells) you need to create a new default template that will load at startup. They didn’t make it easy to do in Office 2007! But here’s how:

  1. Open a new blank worksheet.
  2. Insert > Hyperlink > Paste in any link
  3. Home > Styles > Right click on the ‘Hyperlink style’ > Modify > Set your font options
  4. You probably will want to do the same for the ‘Followed Hyperlink’ style
  5. Delete the hyperlink.
  6. File > Save As > Other formats
  7. Browse to “%APPDATA%MicrosoftExcelXLSTART” (Copy/Paste that into the File name field and press Enter to get there easily)
  8. Save as type: Excel Template (.xltx)
  9. File name: Book.xltx

change hyperlink font in Excel



 

Now whenever you start Excel or create a new workbook, the hyperlink font should be the one you choose.
This Microsoft support article helped me: http://support.microsoft.com/kb/822107 look here for where to save templates for Office 2003, or to see other ways of loading your template.

 

Posted on Leave a comment

How to view more than two places in a Worksheet

The easiest solution is to just open additional windows for the worksheet. Display the View tab of the ruler and click New Window in the Window group (or choose Window | New Window if you have a version prior to Excel 2007).
image
Excel opens additional windows that contain the exact same worksheet. You can then click Arrange All in the Window group of the ribbon to arrange the windows any way desired (or choose Window | Arrange for earlier Excel versions).
image
When you open additional windows in this manner, the windows are independent of each other, meaning that you can scroll them independently. If you make a change in one window, the same change is made in all the windows. (This makes sense, since they all display the same data.)
Once the windows are situated the way you want them, you could save the arrangement as a view – Custom Views in the Workbook Views group of the ruler (or View | Custom Views for earlier versions). That way you could quickly recall the appearance of your windows any time you desire.
image

Posted on Leave a comment

How to delete empty rows

Use the following VB Macro to delete empty rows:

‘/============================/
‘ Sub Purpose: Delect all blank ROWS within the active cell’s
‘ Used Range

Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range
On Error Resume Next
‘only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange
‘calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count ‘# of rows in used area
dbMinRow = rng.Cells(1, 1).Row ‘1st row in used area
dbMaxCol = rng.EntireColumn.Count ‘# of columns in used area
For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
Else
If rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i – 1, 0).EntireRow.Delete
End If
End If
Next i
Set rng = Nothing
End Sub
‘/============================/