Wednesday, May 27, 2020

8 Tips for using Excel as a drawing tool

Over the past several months, I have been playing a factory-building game called Satisfactory. The game involves a lot of number crunching for calculating the input and output rates of the machines. You also have to think about how to optimally arrange the machines and conveyor belts in physical space on the factory floor. Because of this, I've found it to be much easier to design my factories outside of the game so that my in-game time can be spent actually building them!

I had decided to use Microsoft Excel to create these factory designs since I was already familiar with Microsoft Office's shape drawing and image editing tools. For the past ten months, I've been sharing many of my designs with the community via Reddit and Google Drive (I've built up a bit of a reputation for being the "floor plan guy"). I've become quite adept at using Excel for this purpose, so I thought I would share some of the things I've learned.

1. Snap to Grid

The "Snap to Grid" setting is crucial for helping to keep your shapes aligned with the worksheet grid lines.

To toggle it, go to: [ Page Layout tab > Arrange group > Align button > Snap to Grid]

2. Select Objects

The Select Objects tool allows you to select multiple objects by clicking and dragging to draw a selection box. Any object that is fully inside of the selection box will be selected. Objects that are only partially inside the box will not be selected. While this tool is activated, only objects can be selected--the cells inside of the actual spreadsheet cannot be selected.

To toggle it, go to: [ Home tab > Editing group > Find and Select button > Select Objects ]

3. Quick Access Toolbar

Located in the upper-left corner of the window, the Quick Access Toolbar serves as a holding place for your personal collection of favorite commands. Any button that's on the ribbon can be added to this toolbar. I've added buttons for "Snap to Grid" and "Select Objects" because I use these commands so much.

To customize the Quick Access Toolbar, click on the arrow on the right side of the toolbar and click "More Commands".

4. Grouping

Sometimes, you need to create multiple objects to achieve a specific goal. For example, to create the "clipboard" graphic pictured below, I needed:
  • An image for the "board" part of the clipboard
  • An image for the "clip" part of the clipboard
  • An image for the pencil
  • A textbox for the paper

To help manage complex collections of objects like this, you can group them together. Grouping allows multiple objects to be treated as a single object, allowing you to move and resize them as a whole. Even though they are grouped, you can still manipulate each individual "sub" object by clicking on them to select them.

To group a collection of objects, select them all by holding down the Shift key and clicking on each object (or by using the Select Objects tool, described above). Then, go to: [ Page Layout tab > Arrange group > Group button > Group ]. They can be ungrouped by selecting "Ungroup" from the same menu.

5. Nudging objects with the arrow keys

It can be difficult to precisely position an object using just the mouse. The arrow keys on the keyboard can be used to "nudge" an object in a particular direction. Tapping an arrow key will move the object one pixel at a time. Or, if Snap to Grid is enabled, the object will move one column/row at a time.

6. Zoom level issues

My experience has been that the sizes and positions of objects change slightly when you adjust the zoom level. To keep everything precise, I find it helpful to only move and resize objects at a specific zoom level. The zoom level that I work at is 100%.

It's also useful to note that the way Excel renders text can change when you adjust the zoom level. For example, you may have a textbox that wraps in a certain way at one zoom level, and then wraps differently at another zoom level. For example, the images below show the same textbox at two different zoom levels. Notice how the word wrapping changes starting with line 3.



To adjust the zoom level, use the slider in the bottom-right corner of the window or roll the mouse scroll wheel while holding Ctrl.

7. Paste as image

When an object, or group of objects, is copied to the clipboard, Excel will automatically generate a paste-able image when you paste into any image editing program, such as Photoshop or Paint. It even takes transparency into account! For example, if you copy a textbox that has a drop shadow, the drop shadow will render as semi-transparent pixels. This is useful for generating images out of the objects you've created in Excel (as I have done with my factory floor plans).

8. Image quality settings

By default, when you save an Excel file, Excel compresses all images to reduce the size of the file. For example, if you've inserted an image that's 800 pixels wide, and then resized it to be 400 pixels wide, Excel will down-sample the image to 400 pixels. The downside to this is that, if you later decide to make the image larger, it will look distorted because you've lost the original, 800 pixel version of the image.

To force Excel to preserve the original quality of all images, follow the steps below. These settings are applied only to the spreadsheet file you currently have open (they are not global). And if your spreadsheet already has images in it, you'll have to re-insert them to restore their original sizes.
  1. Go to [ File > Options > Advanced ].
  2. Scroll down to the "Image Size and Quality" section.
  3. Click the "Do not compress images in file" checkbox.
  4. Change "Default resolution" to "High fidelity".

Conclusion

I hope that my list of tips help you to "excel" at Excel! Happy spreadsheeting!