Do you know that you can easily align the shapes in your Excel data? It may seem difficult when you are manually changing the alignment of the shapes. But Excel features can be the best aid for this. In this article, we are going to describe 4 easy methods that will help you to align the shapes of your Excel file data. So, let’s begin.
This is an overview image to have a brief idea of the whole article.
📁 Download Excel File
Here is the file that we have used to build this tutorial and you can also use this to learn.
How to Insert Shapes in Excel
Sometimes you may need to insert shapes in your Excel file. Don’t know how to insert shapes in Excel? No problem, just follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, go to the Menu Bar above your Excel sheet.
- From there select Insert option. You will see a drop-down menu named Illustrations.
- At the Illustration option, you will see other menus, and among those select Shapes. Notice the picture to have a clear idea.
- You will find all types of shapes here. Select any of the shapes you want to insert. Here, we have selected the shape of a triangle.
- After clicking on that shape you will see a small +(Plus) sign, place it anywhere in your worksheet where you want to insert the shape.
- Then, click on that, and the shape will appear.
Moreover, you can customize it by size and color.
Learn to Align Shapes in Excel with These 4 Easy Approaches
You can use different built-in features of Excel to align the shapes. There are features such as Snap to Shape, Snap to Grid, and also VBA codes that you can use while aligning the shapes of your dataset. Here, we’ve broken down the processes for you. There are 4 easy ways to align shapes in Excel described below.
1. Using Snap to Shape Feature
In this approach, we are going to use the Snap to Shape feature of Excel. This feature enables you to move the selected objects with reference to another nearby object. Let’s see how to use this feature.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, select one of the shapes that you want to align.
- After that, click on the Shape Format option from the Menu Bar.
- From there you have to click the drop-down Align menu from the Arrange group.
- Then, from this dropdown menu select the option Snap to Shape.
- Now that you’ve applied the Snap to Shape position, Excel will help you to align this object automatically, and position it near the other object while you drag it to reposition it manually.
2. Applying Align Option to Align Shapes in Same Field
There are many alignment options you will find in the Arrange group. You can align the shapes of your Excel file using any of them. Here, we have applied it in four shapes that we want to arrange into two rows. Follow the steps below to learn more about this approach.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select the first two shapes of the first row.
- Then, go to the Menu Bar and select the Shape Format tab.
- From there you will see Arrange group and there you will find the Align drop-down menu.
- As we want to align the first two shapes horizontally, we choose the Align Bottom.
- As a result, the bases of these two shapes come to an alignment.
- Then, we want to align the rest two shapes to be in alignment with the previous ones. So, after selecting the two shapes and following the same procedure, we used the command Align Right to make an alignment of the two shapes in the column.
- Here, the shapes are right-aligned.
- Again, if we want to align another shape to be aligned right with its top shape, we select both shapes.
- Then, from the Align drop-down menu select the Align Right option.
- Repeat this process once again and this time align two hexagon shapes in the bottom with the hexagon by the Bottom Align option.
- Finally, you can notice that these four shapes have become aligned perfectly into two rows.
3. Align Shapes with Snap to Grid Option
The Snap to Grid option is another built-in feature of Excel. You can snap any shapes to this gridline and then it becomes easier to align those shapes. Follow the demonstration below to know more.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select any of the shapes using the Ctrl key from your keyboard.
- Then, go to the option named Shape Format from the ribbon above.
- You will see a group named Arrange, where you will find a drop-down option named Align, click on that.
- From the drop-down menu, select the Snap to Grid option.
- After doing this, you will see that the selected shape is now snapped to the gridline. Turn on the gridlines option from View if it is turned off to understand this.
- Now, you can move this shape around, and it will be automatically snapped by Excel to the nearest gridline. Thus, whenever you try to align shapes by moving them, Excel will only allow you to move shapes along the gridlines. As a result, making alignments will get easier.
4. Utilizing VBA Code to Align Shapes
You can use VBA code to align shapes. Don’t worry if you are not familiar with VBA codes. We have written and explained the formula here for you. You can copy the formula or customize it for your data.
4.1. VBA Code for Horizontal Alignment
Suppose, you want the shapes to be horizontally aligned. Follow the simple steps given below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the sheet and right-click on the mouse.
- You will see the Context Menu has popped up, and select the View code option.
- Now, the “Microsoft Visual Basic for Applications” window appears.
- From that window, you have to click Insert and select the Module option from there. As a result, a module will open.
- In that module paste the code given below.
Sub AutoAlign_Shapes_to_Horizontal()
Dim shape1 As Shape
Dim length As Long
Dim double_Top As Double
Dim double_Left As Double
Dim double_Width As Double
Const double_Space As Double = 8
If TypeName(Selection) = "Range" Then
MsgBox "Select the shapes first."
Exit Sub
End If
length = 1
For Each shape1 In Selection.ShapeRange
With shape1
If length > 1 Then
.Top = double_Top
.Left = double_Left + double_Width + double_Space
End If
double_Top = .Top
double_Left = .Left
double_Width = .Width
End With
length = length + 1
Next shape1
End Sub
🔨 Code Breakdown
👉 First check if the shapes were selected or not by the line If TypeName(Selection) = “Range”. If no shape was selected then print this message “Select the shapes first.”
👉 To loop through the selected ranges we have used For Each shape1 In Selection.ShapeRange.
👉 Then, the If function is used along with .Top = double_Top, .Left = double_Left+ double_Width+ double_Space, which means if the selected shape is not the first shape then move it to right and align it to the top.
👉 After that, use the next three lines, double_Top = .Top, double_Left = .Left, double_Width = .Width is used to store the measurements of the shapes.
👉 Lastly, take a count of the shapes by length = length + 1.
- Now, close this window and get back to your data sheet, and from there select all the shapes using the ctrl from the keyboard.
- Then, from the ribbon click on Developer, and from that select Macros.
- A window will open, from there select the Macro named as “Aligning_Shapes_Horizontally” and then click on Run.
- After the code has run you can see that all the shapes are now aligned horizontally.
4.2. Using VBA Code for Vertical Alignment
Again, if you want to align all these shapes vertically you can write different VBA codes for this. Let’s discuss this below.
⬇️⬇️ STEPS ⬇️⬇️
- First, right-click on the sheet, and then you have to choose the View Code option from the Context Menu that has appeared, as you will see.
- After that, a window labeled “Microsoft Visual Basic for Applications” will appear.
- Then, you must click Insert from that window and then choose the Module option.
Consequently, a module will launch.
- Now, copy and paste the following code into that module.
Sub Align_Shapes_to_Vertical()
Dim shape1 As Shape
Dim length As Long
Dim double_Top As Double
Dim double_Left As Double
Dim double_Width As Double
Const double_Space As Double = 8
If TypeName(Selection) = "Range" Then
MsgBox "Select the shapes first."
Exit Sub
End If
length = 1
For Each shape1 In Selection.ShapeRange
With shape1
If length > 1 Then
.Top = double_Top + double_Width + double_Space
.Left = double_Left
End If
double_Top = .Top
double_Left = .Left
double_Width = .Width
End With
length = length + 1
Next shape1
End Sub
🔨 Code Breakdown
👉 Firstly, in the lines If TypeName(Selection) = “Range” Then, MsgBox “Select the shapes first.” The TypeName(Selection) = “Range” tests whether the user has selected a range or not. So, if the user-selected thing is not a range, that means not an object then it will print the message “Select the shapes first.”
👉 Then, return all the shapes in the selection Selection.ShapeRange is used.
👉 If length > 1 means, if the shape selected is not the first shape then slide it below the previous one and align it to the left. This is executed by the lines .Top = double_Top + double_Width + double_Space, and .Left = double_Left.
👉 To save the characteristics of the shape double_Top = .Top, double_Left= .Left, double_Width = .Height these lines are used.
👉 Finally, to add a shape count we used length = length + 1.
- Then, you can return to your data sheet and use the ctrl key to select all the shapes there.
- After that, from the ribbon, go to the Developer then click on Macros to open a window.
- Here, you will select the Macro Name which is Align_Shapes_to_Vertical, and click Run to run the code.
- Finally, after the code has been executed, all the shapes are horizontally aligned.
How to Space the Shapes Equally Using Distribute Option
Suppose, you have more than two shapes in your datasheet, and you want to space these shapes equally. There is a feature in Excel called Distribute. Let’s see how to use this option to insert an equal space among shapes.
⬇️⬇️ STEPS ⬇️⬇️
- To start with, select all the shapes that you want to place in an equally spaced position.
- Then, go to the Shape Format option. When you get there, you will see a group called Arrange and you will find a drop-down menu called Align; select it.
- From there, select Distribute Horizontally if you want to insert equal space among the shapes horizontally.
- See how these shapes are equally horizontally spaced.
- But in case, if you want to space shapes vertically you can also choose to Distribute Vertically instead.
- Finally, notice that these shapes are now arranged in an equally spaced manner.
Switching Front and Back Positions of Shapes
If you ever find that shapes are together on multiple planes in Excel and you want to change the shape position from back to front or vice versa you should go for the steps below. Suppose we want to bring this green rectangle in front.
⬇️⬇️ STEPS ⬇️⬇️
- Start by right-clicking on the green shape and selecting Bring to Front from the Context Menu.
- After that, according to the command you can see that this shape is now at the front.
- Or maybe you can take it backward again. Right-click on this shape again and from the menu click on Send to Back option.
- Therefore, the full shape will again be sent to the back of other shapes.
📝 Takeaways from This Article
📌 You can get an idea of how to change shape alignments using different built-in features of Excel.
📌 You have learned how to write VBA code to align shapes vertically and horizontally.
📌 Can have an overview on how to insert equal space among the shapes.
Conclusion
Fixing the relative alignments of shapes can be a tiresome task if you attempt it manually. Instead, we suggest you try different options of Excel to make your work easier and save you valuable time. This article has discussed all the methods that you will need to complete this task in a short time. For more articles, visit our website ExcelDen.