VideoPhoto

30 September, 2021

How to Navigate in Excel Worksheet

Moving around a worksheet made easy. Just fix in memory several intuitive  shortcuts/clicks, as illustrated in this sketch, and you can move swiftly to any place in your even very large data range, and beyond.

Current data range area (a table) is shaded in light green colour here.

In a list format these shortcuts can be summarized as follows:

  • Arrow keys (right,left,up,down)  - move by one Cell
  • ALT+Pg Up / Pg Down               - move by one screen to the Left/Right
  • CTRL+Arrow keys                     - go to the edge of data range
  • CTRL+End key                           - go to the last cell boundary (Row/Column)
  • CTRL+Home key                        - go to cell A1
  • Home key                                    - go to the beginning of a Row
  • Pg Up / Pg Down key                 - move by one screen Up/Down
  • Double-click on any edge of active cell - go to any edge of current data range


28 September, 2021

TIPS for CLICKS: Cell Borders with Shapes

Borders around Excel cells make worksheet contents visually enhanced and easier for evaluation. They clarify the format and layout of the data, make it appear more orderly and also highlight important information. Drop-down menu of Borders provides multiple pre-built options for that, as you can see some of them here:

However, some Excel users sometimes would like to apply different type of borders (e.g. rounded-corner borders) instead of the square-cornered ones. There is no such an option in Excel. Fortunately, there is a workaround available for such unconventional type of 'borders'.

Using Camera tool 📷

Although there are very versatile snipping tools available outside Excel, like e.g. "Snip and Sketch" in Windows, the Excel Camera tool (📷) is still very useful in some situations when working on your workbook. This is due to a dynamic nature of the Camera snapshots. When you change something in the source of your worksheet, it'll be changed automatically in the pasted snapshot, too.

So, using the Camera tool is very helpful, especially in these areas:

  • creating dashboards,
  • watching particular areas/cells in a workbook,
  • adding in-cell charts,
  • printing multiple ranges of your workbook on a single page, 
  • pasting Excel ranges/objects in other applications.

If this tool is not included in your ribbon, it's worth to add it to the Quick Access Toolbar at the top of Excel screen. Adding it is a simple procedure, click on File>Options>Quick Access Toolbar>All Commands, find and select Camera, and add it to the Toolbar.

You can take snapshots of any cells/areas of your worksheets, including charts and other objects. However, remember that in the case of charts you must create shots from the range of cells covered by your chart, not from the chart itself! When you change chart position, the snapshot will be changed regarding to the cells data, not to the chart.

26 September, 2021

RAND() function: Distribution of the ratios of TWO Rand() functions

Excel Rand() function generates a random real number in a standard continuous  UNIFORM distribution of less than 1 and equal/greater than 0. It means that in this distribution every value between 0 and 1 is equally likely to be chosen.The mean of the distribution is 0.5. The variance is equal to 1/12 (=0.083333).

The Rand() function is used mainly as a random number generator. It's distribution is pretty simple and straightforward. Much more interesting are distributions of quotient (ratio), product, difference or sum of two independently run Rand() functions. Their usefulness in practical applications is not yet well defined.

I've looked closer at the distribution function of the quotient, denoted here as F(z), where z=X2/X1. The result of distribution, based on the sample of 2000 outcomes (vertical axis) is presented on the following chart (z on horizontal axis):


24 September, 2021

Data entry tips: Formulas and Dates

You might learn from some sources that when you enter a formula into a cell in Excel, you have to start your entry always with the equal sign. This is not true. You can, but DON'T NEED to start with the '=' sign.

Each formula can be started also with either '+', or '-' (if you mean negative number/expression) signs. When you press the ENTER key, Excel will voluntarily add the equal sign for you.

Here are couple of examples to illustrate what happens:

 

23 September, 2021

Tips for Clicks: CELL tricks

Elementary: what is the Excel cell?

It is much more than "rectangular-shaped box on a worksheet", where you can enter any combination of numbers or words...

First of all, it can be rectangularly- as well as squarely-shaped. Secondly, you can enter into a cell (within specified limits) not only any kind of text and any kind of graphics, but also pictures and drawings, and enter them in many layers, displayed or hidden.

Here is an example of content fitted in just ONE cell, in multiple displays.

19 September, 2021

VLOOKUP function and UDF

Normally you use the VLOOKUP function to find things in a table or a range by row. E.g., look up a price of a mower part by the part number The function works fine for a specific table in a given worksheet.

But, let's say, you have a workbook with related several tables located in different worksheets. You might need to check all worksheets to find a match for your lookup value. In such a case you would need to use the VLOOKUP repeatedly in each of the worksheets. Much more efficient would be using this UDF (User Defined Function) I've created to make the task easier:

Function VLOOKUPinWBK(lookup_value As Variant, table_array As Range, _
    col_index_num As Integer, Optional range_lookup As Boolean)

'This UDF looks in ALL worksheets in your workbook and finds the
' first match for lookup_value.

Dim wSh As Worksheet
Dim wks As String
Dim vLooked
On Error Resume Next
For Each wSh In ActiveWorkbook.Worksheets
    With wSh
        wks = wSh.Name
        Set table_array = .Range(table_array.Address)
        vLooked = WorksheetFunction.VLookup(lookup_value, table_array, _
            col_index_num, range_lookup)
    End With
If Not IsEmpty(vLooked) Then vLooked = vLooked: Exit For
Next wSh
Set table_array = Nothing
VLOOKUPinWBK = vLooked & " (found in '" & wks & "')"
End Function