Showing posts with label cells. Show all posts
Showing posts with label cells. Show all posts

24 February, 2022

Traps of Custom Formatting

Numbers can coexist in Excel cells with text strings. What may surprise you is that the contents of such cells can be treated and used as numbers. It means that they can be used as ordinary numbers in mathematical calculations, functions and formulas. This happens when you apply some kinds of custom formatting for cells.

Here is such example of cell formatting and some confusing results of using it in formulas involving text strings.

I've used the following custom format for cells in column A:

30 January, 2022

Unique Macro Buttons - Unlimited

If you use macros (VBA code) in Excel you probably use macro buttons as well. There are many ways to create them, but the one I like the most is to utilize just the Excel cells. Yes, nothing else but the cells. Cells are 'pictures'. Obviously, if you'd like to "decorate" them in any way, you could; and at least you'd probably like to mark them somehow in order to recognize that they hold your macro code.

So, you'd start with selecting a cell and entering some (centered) text into it, e.g. 'Run abc...'. Next, to create the button, you'd:

  • copy the cell (using CTRL+C shortcut) and paste it to the same cell (or another - it's your choice) as a Picture (using ALT+H+V+U shortcut)
  • right-click in the cell and select Size & Properties > Properties and select Move & size with cells option (this way the 'picture' will always fit and stay in the same cell)
  • right-click again, select Assign Macro... from the menu and select your macro from the list you'd see in Assign Macro dialog

The cell is now your macro button. Click it to run your assigned macro.

If you'd like to make the button more distinct (visible) you'd add some shape or photo or icon and/or format the cell at your will (prior to pasting it (!) as a Picture). Here is a couple of ideas, if you want to make the button unique:

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.