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

11 September, 2021

TIPS for CLICKS: Fit the column width to a cell contents

Do you know that you can easily set the Column width in your worksheet to fit the contents of your specific cell in the column? 

You can do that with Excel shortcut. Just select your target cell with some content, then press and hold ALT key, pressing consecutively O,C,A. You've just entered the shortcut:

Your column width has changed to fit the content of the selected cell. If there is nothing in your selected cell the column width won't change.

 

08 September, 2021

Goal Seek: Solving cubic equations

Let's say you you know the desired result of some formula, but you need to find possible input value(s) to achieve that result. You may even know the approximate values of those parameters. However, it's probably nothing more than guessing, and you need exact values.

In such cases it's better to use Excel's Goal Seek tool which helps to arrive at exact solutions very efficiently.

I'm providing here an example of using the Goal Seek. The example shows how to setup your worksheet for solving a cubic equation, such as Ac*x^3+Bc*x^2+Cc*x+Dc=0, where I renamed the coefficients a,b,c and d to Ac,Bc,Cc and Dc.

In this case we want to find the roots for this specific equation:

2*x^3-15*x^2-195*x+990=0

06 September, 2021

TIPS for CLICKS: Date and Time STAMP

There are many ways of creating a date stamp or/and time stamp in Excel. You can check them e.g. in these pages:

In most cases - unless you need a time stamp which displays seconds - you can use Excel shortcuts for both date and time:


CTRL+SHIFT+:  (colon) shortcut for your time stamp,

 

and

CTRL+;  (semicolon) shortcut for the date stamp.



These two shortcuts provide STATIC (non-refreshable) stamps; they are not updated whenever 

anything changes in your worksheet.

The NOW() function is OK, but it inserts DYNAMIC (refreshable) stamp, so it requires conversion 

from function to a value format to become static.

 

 




04 September, 2021

How to DATE in Excel

This is about dating with the Excel DATE... function, and with its versatile formats and forms of uses. It's syntax is simple but the results of its application can be sometimes a bit confusing or unexpected.

Confusing - because the DATE function with its syntax DATE(year,month,day) doesn't object some strange parameters and accepts numbers such as:

  • negative, 0, and >12 integers for a month,
  • higher number of days than the number of days allowable for a given month, as well as 0 days,
  • negative numbers for a day,
  • number lower than 1900 for a year.

So, e.g. entering accidentally month number 14 would set the date to February of the following year. Entering month number 0 would set the date to December of the previous year. Entering year number 1888 would set the date to the year 3788.

Normally, i.e. in General format, DATE function returns a serial number starting from January 01 in the year 1900, represented by number 1, which was Sunday, and increasing by 1 for every next calendar day. September 05, 2021 is the 44444  special day in this sequence, and it's Sunday as well.

If you need to deal with dates before the year 1900 you can access relevant information here:

MrExcel