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

05 August, 2023

Shapes Based On Johan Gielis's Superformula

The Gielis's Superformula is used to model variety of abstract and natural shapes. It finds application in biology, technology, mathematics, and physics.

I'm providing here a small representation of shapes created with my relatively simple VBA macro listed at the end of this post. You can experiment with different values of the parameters a,b,m,n1,n2, and n3 to get all kinds of shapes and find some useful models. To control the resolution of your shapes you can adjust the number of points, nP. Optionally, I've applied some Excel formatting of the plots.

Sub SuperformulaPlot()
'Generates Gielis's Superformula plot
'Calculates X and Y values and assigns them to the chart series
'Creates a 2D scatter plot; no markers

Dim nP As Integer, i As Integer
Dim xVal() As Double, yVal() As Double, theta As Double, r As Double
Dim a As Double, b As Double, m As Double, n1 As Double, n2 As Double, n3 As Double
Dim chartObj As ChartObject
nP = 500    'You can adjust the number of points to increase/decrease the resolution of the curve
a = WorksheetFunction.RandBetween(1, 5) 'Random parameters a,b,m,n1,n2,n3; experiment!
b = WorksheetFunction.RandBetween(1, 5)
m = WorksheetFunction.RandBetween(1, 10)
n1 = WorksheetFunction.RandBetween(1, 10)
n2 = WorksheetFunction.RandBetween(1, 20)
n3 = WorksheetFunction.RandBetween(1, 10)
'a = 1: b = 1: m = 2: n1 = 5: n2 = 5: n3 = 5    'just some default values
ReDim xVal(1 To nP)
ReDim yVal(1 To nP)
For i = 1 To nP
    theta = 2 * WorksheetFunction.Pi * (i - 1) / nP
    r = (Abs(Cos(m * theta / 4) / a) ^ n2 + Abs(Sin(m * theta / 4) / b) ^ n3) ^ (-1 / n1)
    xVal(i) = r * Cos(theta)
    yVal(i) = r * Sin(theta)
Next i
'Create a 2D scatter plot
Set chartObj = ActiveSheet.ChartObjects.Add(Left:=120, Width:=500, Top:=10, Height:=400)
With chartObj.Chart
    .ChartType = xlXYScatterSmoothNoMarkers
    .SeriesCollection(1).Values = yVal      'Add data to the chart
    .SeriesCollection(1).xValues = xVal
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Text = "SuperPlot "& a & "-"& b & "-"& m & "-"& n1 & "-"& n2 & "-" & n3 & "; "& nP
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y"
End With
End Sub

29 July, 2023

Shapes Based On Equations: Cardioids

Cardioid curve is formed by tracing a point selected on the circumference of a circle, rolling onto another circle of the same radius. Here are two examples of cardioid graphics, one is just a single cardioid curve and the other is presentation of 4 cardioids positioned within the same coordinates.

These kind of graphics can be generated using the VBA macro listed below the two charts.

22 July, 2023

Excel Comments: Setting Font Format

Default font format in Excel Comments may look like this:

If you want to change some of the font settings (i.e. the Windows font settings), you can either try to adjust font format in "Display settings" of Windows or use VBA code listed below. The second option is better, because it doesn't change font settings in all other Windows applications. Remember that you must make your Comments visible. If necessary, right-click in the cell and click on Show/Hide Comments option.

This is the event driven macro. To use it, you need to:

  • open the Visual Basic for Applications (VBA) editor in Excel (use the shortcut ALT+F11 on your keyboard)
  • click on the View in the top menu and choose Project Explorer option
  • in your VBAProject double-click on Sheet1 and paste the macro code in the window
  • close the VBA editor and return to your Excel worksheet

16 July, 2023

Excel Shapes: Positioning, Formatting and Layout In Worksheets

Excel Shapes are frequently used to enhance visual appeal of worksheets as well as to convey information. It's relatively easy to work with the Shapes. You can insert, resize, rotate, format, and position them within the worksheet, according to your needs.

This post is about special case of positioning and manipulating of Shapes. Namely, I'll show the way of inserting and moving them so that they are located/centred precisely at the crosspoint of 4 surrounding it cells. Furthermore, in this case I'll use a worksheet with squared cells. If you need to know how to make cells square please refer to one of my previous posts (Make cells SQUARE) .

Here are just two examples showing what I'm talking about:

These are the formatted Shapes called "Donut" and "Gear with nine teeth". They are placed exactly at the point where the four cells meet together. It's not that easy to do that by hand, so I've created the following Excel macro, to handle this task swiftly:

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.