VideoPhoto

19 August, 2021

TIPS for CLICKS: Working with Functions and Formulas

Excel FUNCTIONS = Predefined formulas used for specific values in a specific order. They operate usually on data entered in range of cells and increase the Excel users productivity. Total number of the functions available in Excel is close to 700.

Some of the most important of them (most frequently used) are: SUM, IF, MIN, MAX, AND, OR, AVERAGE, COUNT, DAYS, VLOOKUP, DATE, ... etc. Examples of functions working with text: UPPER, LOWER, PROPER,... etc.

Excel FORMULAS = Expressions operating on values in a range of cells and operators. Can utilize several Excel functions and work with both numeric and textual data. ARRAY formulas are a special type of formulas, and can be very complex.

Here is the list and description of some of the most useful shortcuts used in conjunction with Functions and Formulas. I hope you'll find them helpful.

16 August, 2021

TIPS for CLICKS: Formatting / Editing / Moving around in a Workbook

Excel is equipped with hundreds of formatting and editing tools, key codes and alternative handling of data. Some of them are worth more useful than others. The following list presents some of the most helpful and time saving tips for frequent users.

Format Painter > If you double-click the Painter in the ribbon you can copy formatting of your selection (cell, range) repeatedly or singularly into multiple disparate cells or ranges. Click Format Painter once to quit it, if necessary.

Multiple lines in a cell > Two or more lines can be inserted into a cell by pressing ALT+ENTER after entering some value in the original line. It's just another way of wrapping text in a cell.

Borders >  If you select (in Home tab) Borders>More Borders..., you can select and add diagonal lines in active cell or range of cells to divide them e.g. in two parts. Then, you can add some value/text into the cell and press ALT+ENTER to type another value/text in the second line. Alignment of the entries can be fixed using spacebar, if needed. Example:

15 August, 2021

TIPS for CLICKS: Using drop-down lists for data entry

This is an excellent helper for data entry in Excel. When we create worksheets with tables of data or databases, in many cases there are text entries, expressions or phrases that we have to enter repeatedly many times. In such situations we can create lists of entries, like people names, phone numbers, cities, days of the week, months, whatever is included in tables, and then create drop-down lists based on the entries. Later, instead of typing the entries one by one in our table we just select the entries from the drop-down list.

Here is a simple example, based on the list of fruits. Follow the steps below to create the list.

14 August, 2021

TIPS for CLICKS: Data entry

There are plenty of shortcuts provided in Excel program for data entry. Their purpose is twofold:

  • to reduce data entry time, and
  • to reduce data entry mistakes, to avoid errors

I don't think that average Excel user can remember or need to use all of them. However, several of those shortcuts are helpful for almost all users and are worth remembering.

Here is my very short list of shortcuts for data entry and basic editing.

08 August, 2021

Workbook design: basic principles

Design your workbook for both EFFICIENCY and LOOK & FEEL.
  • First of all - plan it well for your specific purpose, in order to avoid complications down the road. In cases where there is complexity of data and calculations, use multiple worksheets within the workbook and label them properly (name them by their purpose) to separate clearly data input, assumptions, calculations, output, tables, charts, etc.
  • Use highlighting/shading to differentiate, in particular, data entry parts (input) from outcome (results, report).
  • If you design the workbook for multiple users, add instructions and documentation, record of changes, wherever needed.
  • Use Comments or Callouts features to add meaningful explanatory notes to some cells, e.g. to explain more complex calculations, especially for external users.
  • Break formulas involving complex calculations into multiple cells to make any troubleshooting much easier. Use cell references rather than numbers (constants) to avoid errors related to input. Any assumptions must be clearly visible in worksheet layout.

05 August, 2021

How to highlight space between two plotted curves / lines

If you want to enhance your Excel charts, to fill with color some target range of the data, this procedure may be helpful.

I'm providing here two examples of such enhanced charts. In addition, I've included procedure for calculation of the surface area bounded by the two curves/lines.

Chart #1

01 August, 2021

Weather forecast in Excel

If you need to keep your eye on weather conditions in several or more places in your country or in the world, it may be convenient to have an easy access to actual detailed forecasts in Excel workbook. It's relatively easy to create Excel worksheet with access to forecasts for locations of your specific interest. Here is a fragment of such worksheet I've created for my personal use:

The QR codes refer to weather forecasts for locations shown underneath. You just click on selected QR square to display the forecast for the location. For example: