VideoPhoto

25 December, 2021

COLORS in Cells: How to Get, Set and Use them

It's really easy to color Excel cells, their background, content or borders. We can do it either directly (using Format Cells... or some Font options) or indirectly - using Conditional Formatting in a variety of available ways in the Excel menu.

When it comes to determining (getting) exact codes/names of colors being already used in a worksheet, it can be a bit complicated. And it may happen that you need to know what colors were originally used for background filling of some cells. Well, we can't find it out by using any Excel function or formula. So, practically, we must use macros (VBA codes) to solve such puzzles. E.g., the following code will determine Color Index for background color of cell A2.

Press ALT+F11 keys to display the VBA editing window and enter there this code:

Sub getCellBG()
Dim getC as Integer
getC=Range("A2").Interior.ColorIndex
End Sub

16 December, 2021

How to create Activity Tracker / Timer

If you'd like to keep in Excel the record of your common daily tasks, in terms of disciplined use of your valuable time, then the solution I'm providing here can be helpful. It makes easy recording of duration of any routine daily activities and provides basis for further analysis of any kind.

This is how it looks like in an exemplary edition:

03 December, 2021

WeekDay and BirthDay

You know your birthday date, no problem. Do you know on which day of the week you were born? If not, you can find it out quite easily in Excel. There are many ways to do it, but probably the simplest one is to use the WEEKDAY function, as shown in this snip:

30 November, 2021

How to count any characters and digits in a text string using ARRAY formula

Let's say you need to find out how many characters, like e.g. a, or b, or w, or any other alphabetic character, or even digits, are there in a text string subject to your analysis.

Here is an example of a string located in cell A2:

Security guards at the hotel make sure guests remain in isolation, while police outside ensure no one enters the premises.

I want to count all characters (i.e. determine their frequency), from a to z (and A to Z) present within that string. This snip shows how easily it can be done using the ARRAY formula presented here:

24 November, 2021

Summing up a variable number of the Largest or Smallest values in a range

Here is a range of numbers provided as an example. It could be any unsorted range/list of numbers you're working on:

Your data set is large and for some specific analytical reason you'd like to track the sum of several largest and smallest numbers in your set. The ARRAY formulas come to help and are very efficient in such cases.

22 November, 2021

SUMMING UP selectively based on references - using ARRAY FORMULAS

Some business sells some products, e.g. vegetables, to various countries/ merchants. Records of sales are kept in Excel table, like in this simple example:

14 November, 2021

How to retrieve some of the LONGEST/SHORTEST TEXT entries from Excel list

Suppose you want to find the longest or shortest entry from a long list of text strings. Or, maybe you need to retrieve the 2nd, or 3rd or 4th longest/shortest entry from the list. How to do it?

Let's start with this small fragment of a big table, for example:

The first column of this table contains the subject list of arbitrary textual entries. All other columns are provided for explanatory purpose only.

How to find some LARGEST or SMALLEST numbers in a range

There are situations when we need to find out what is the highest or the lowest value in your table or any array of numbers. And sometimes we may need to determine what is the second or third highest or lowest value in a given range. In all such cases ARRAY formulas are very helpful, so I'm providing some examples of using them.

Let's look first at finding the highest/lowest values in an array/table of numbers. Here is a small sample range of numbers:

11 November, 2021

Array formulas summarize tables with multiple conditions

Let's assume that you are using Excel table for recording sales of some products by several agents. From time time you need to check how your business is doing.

The table is getting larger and larger. I set its size arbitrarily to 1000 rows, but this is up to the user needs. Here is just its small fragment:

08 November, 2021

CELLS: Blank, Empty, Space, Zero

Back to basics... 

What's the meaning of all these basic terms in Excel?

BLANK cell: empty or not? Not quite, may contain "" (zero length text string)

EMPTY cell: nothing is there (no text string, no zero); always evaluates to zero

Cell with SPACE: contains 'space' character (=" ")

Do you know that: 

02 November, 2021

VLOOKUP without limits (case-sensitive): using IF+ INDIRECT+MATCH+EXACT+INDEX functions

Here is a small data table, as an example of Excel table, created for the purpose of this exercise:

What if you are working with a real very big table, let's say 20 columns and 20000 rows, and want to retrieve quickly some specific information from such table, from any row and any column. The following table provides some examples of lookups - based on my small table - you'd might want to do:

26 October, 2021

Find ADDRESSES of Specific CELL CONTENTS in Excel Workbook

Let's say we are dealing with Excel table several columns wide and hundreds and hundreds, or even thousands of rows long. Just for illustration I'm providing here a small 'fake' table, a fragment of a big one:

Working on such a large table we may need to find addresses of cells containing some specific values, name, date, number, etc., and we need to look for them quickly.

25 October, 2021

Header/Footer: What can be entered there?

Excel offers the following built-in headers and footers. They can be added to your worksheet easily, with a single mouse click.

However, we're not limited to these options. In fact, you can enter any text/string into your worksheet cell and add it to any part of the header or footer with just one line of VBA code of a macro. E.g., this line will add contents of cell B2 to the left part of a header:

ActiveSheet.PageSetup.LeftHeader=Range("B2").Value

Such a line can be included in the VBA code I'm presenting at the bottom of this post.

20 October, 2021

In Reverse: Going Backwards with ARRAY formulas

Do you need to type or use expressions or numbers in reverse?

Trivial or not, in fact we need sometimes to display or read some numbers or text / expressions in reverse. There are several options available in Excel to do just that. You can do it in a macro way or using some known Excel formulas. E.g. you may use the following ARRAY formula for reversing digits in a number:

=SUM(VALUE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))*10^(ROW(INDIRECT("1:"&LEN(B2)))-1)) 

However, at a closer look, the formula works fine with digits only. If you enter 12481632 into cell B2, it returns 23618421, but after entering 2.35 it returns error #VALUE! . In addition, it doesn't return trailing zeroes.

So, after some experimenting, I've created my own dynamic ARRAY formula. Here is its exemplary application:

The formula reverses whatever string, number or a mix of letters and digits you'd like to reverse. Just remember: this is an ARRAY formula, so - after typing it in - you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER on your keyboard.

 

17 October, 2021

TOP TIP: Counting Cells Containing Numbers Meeting Specific Criteria

Most of the time we organize our data in orderly Excel tables making their analysis pretty straightforward. Sometimes, however, we may have to deal with chaotic sets of numbers located all over different ranges/worksheets, like in this somewhat bloated example:

How to count and analyse them? Couple of examples follow.

14 October, 2021

How to Check if the Contents of Two Ranges/Worksheets is Different

You are not sure if some ranges of data in your worksheet, or in two different worksheets, contain the same data. How to  figure it out? And if they are different, is the difference significant?

Use ARRAY formula to check it out. You can count the differing cells using e.g. this kind of array formula in Sheet1 to compare with cell contents in Sheet2:

=SUM(IF(B2:G100=Sheet2!B2:G100,0,1))

Remember that to enter it you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets.

Also, make sure that the size of compared ranges is the same. Location, however, can be different, like e.g. A2:F20 in one worksheet and C5:H23 in another.

13 October, 2021

To COUNT any Characters or Strings in any Range of Cells

Use  an ARRAY formula. You can find out the number of occurrences of anything (e.g. BS, 2016, s, graph, three days, 50%, etc.) within a given cell or a range of cells.

Assuming that your data range is e.g. A2:D100, select any cell outside that range and type the formula similar to this one:

 =SUM((LEN(A2:D100)-LEN(SUBSTITUTE(A2:D100,"Fig.","")))/LEN("Fig."))

and simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets. And the cell where you array-entered the formula will show the result, like here in cell G1:

12 October, 2021

Extract Formulas to External File

If your workbook/worksheet is full of Excel formulas it may be worth to keep track of them in one place, in a compact listing, in a separate text file.

Here is a simple short macro that serves the purpose:

Sub ListFormulas()
Dim rng As Range
'Extracts all formulas from a worksheet to a text file (Notepad/Wordpad)
Open "C:\Users\Adam\Desktop\XFormulas.txt" For Output As #1
For Each rng In Sheets("Formulas").UsedRange.Cells
If rng.Formula <> "" And Left(rng.Formula, 1) = "=" Then
           Print #1, rng.Address; Tab; rng.Formula
End If
Next
Close #1
End Sub

10 October, 2021

Open Workbook with Startup Worksheet

If you create a workbook that will be opened and used frequently by yourself and - especially - if you design it for sharing with other users, you may want to start it in a compelling, attractive way.

I'm presenting here an example of initializing a worksheet which you might want to appear when the workbook is being started. The workbook is macro-driven, so it must be saved as Macro-Enabled Workbook. This is how the worksheet looks like after loading with a randomly selected photo (this is a screenshot of the left part of my computer screen):

08 October, 2021

Function N() = Insert Comment: TRUE or FALSE

When we return to our workbook created some time ago and try to revive/use it, it quite often happens that we've forgotten what we had intended to achieve by composing some complex formulas or entering even simple data points. We can avoid such situations in the future by adding comments or notes to some cell contents.

The Insert Comment feature is OK, but in most cases the better choice is to use the N() function, as illustrated below:

 

04 October, 2021

TIPS for CLICKS: About using Mouse in Excel

Hello, mouse!

  • Click means here: left button click 
  • Fill Handle in Excel means: small black rectangle in the lower right corner of a cell/range selection.

The following table provides 30 helpful tips related to the use of mouse in the Excel app. It covers the most useful mouse 'shortcuts' enhancing user's skills and productivity, and explains how to use them. It's worth to utilize them in your work.

01 October, 2021

Keyboard Functions: Cheat Sheet

All in one place, practical reference to your keyboard functions. Very helpful for every user of Excel, especially in situations when you can't use your mouse.

This is what you can do with your keyboard Function Keys, when combined also with control keys (Ctrl, Alt, Shift).




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