Showing posts with label formatting. Show all posts
Showing posts with label formatting. Show all posts

17 February, 2022

Picture in Picture in Excel Worksheet

What if you need to highlight/call attention to any fragment of your worksheet, be it a piece of data table or a picture/chart, for a presentation or just for printing a report?

You can do it quite easily in Excel. Let's consider a picture. You may need to overlay an enlarged small fragment of your picture onto the original whole picture. To do that you can use the Windows "Snip & Sketch" utility (the shortcut to invoke it, is: Windows Logo key + SHIFT + S). You can select either rectangular or free-form snip. After getting the snip, format/enlarge it as needed, in a way attracting attention to it, and move it to a desired position. This is an example of the 'picture in picture':

The same can be done also with any chart/graphics.

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'.

25 August, 2021

TIPS for CLICKS: Working with Excel Tables

Excel Tables are one of those basic, most useful features used for data recording and analysis. Excel makes its tables smarter, more versatile, cognitive, and easy to use all the time.

However, if you spend lots of your time working with Excel tables it's worth to remember all keyboard and mouse shortcuts available for tables. They help to use the tables more efficiently and save some time. Here is my collection of the most frequently used shortcuts:


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:

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.

17 May, 2021

Presentations in Excel

If most of your work, you do for presentations, comes from Excel, there is no reason to use the PowerPoint instead of Excel itself for preparing your demonstration.

In fact, all your charts, tables, forms, textual info, background graphics, and even sounds, can be quite easily presented and put in order in Excel worksheets.  All the hassle with extra work of copying and pasting into PowerPoint could not be necessary.

Let's say, you've carried out your data analysis and prepared workbook with 30 perfect  worksheets ('slides') for your quarterly presentation. How would you proceed to get ready for reporting your work? Switching to PowerPoint? Not necessarily.

If you'd decide to stay with Excel, the following steps could probably be more efficient way to go. 

First, put all your presentable worksheets in consecutive order in which they'll be presented.

Add a blank worksheet dedicated to a title page. Excel has all the tools needed to create an attractive design. Such a worksheet could show just the subject of your presentation or might be a bit more elaborated, as e.g. in this figure:

Similarly, you can add another worksheet for displaying "The end" page, if appropriate.