Categories
Self-organization

How to create a tornado screw pattern in microsoft excel

In this tutorial we’ll walk through the steps to create a simple but informative Tornado Chart in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a set of data showing years, assets, liabilities and net worth:

And from this data we are going to create a Tornado chart like this:

The first thing we’ll do is copy the data to a new worksheet. Then I’ll create a separate set of data for the year, asset and liability columns, but change the liability column to be equal to the negative of the current liability values:

Then I’ll highlight this new set of data and choose to create a 2D stacked bar chart from the Home tab:

After some positioning and resizing, the chart looks very similar to what we want to accomplish. Now we only need to do a few tweaks and we’re all set!

Next, I’ll right click on the vertical axis and choose format, Then I’ll go the the Label section and choose to position it over to the left by choosing “Low”:

Notice at this point that the values are in the reverse order from what my data set shows – the data goes from 2005 to 2014, while the chart has those in the reverse order. To change this, I need to check the box for “Categories in reverse order” under the Axis Options section:

While this vertical axis data is still selected, I’ll use the font increase command on the Home tab to increase the font size so it’s easier to read.

Next, I’ll select the horizontal axis at the top and do two things: change the location to “High” and the Display Options to “Thousands”:

I’ll also kick up the font size so it looks a bit better.

Next I’ll change the font code so that the negative values appear in Red and in parentheses, with a comma separator for thousands. You’ll need to click the Add button for this to take affect:

Finally, just right click on a liability bar and an asset bar to choose the color you want them to be:

And there you have it – a great looking Tornado Chart!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

It’s tornado season. Don’t freak out, I am talking about Excel tornado charts. Use them to visualize age and gender-wise KPIs. Here is a quick demo of interactive tornado chart made in Excel. Watch it and read on to learn how to make your own tornado in a spreadsheet.

How to Create a Tornado Screw Pattern in Microsoft ExcelDemo of tornado chart in Excel – with slicer interactions

When to use Tornado charts?

Tornado charts / population pyramids are very useful in below situations.

  • See population distribution by gender (obviously)
  • Purchase patterns by gender and ages
  • Customer walk-ins by gender and time of day
  • Distribution of time on page by visitor type (customer / prospect)
  • Units purchased by customer types (international vs. domestic)

In general, you can apply this type of charts whenever you have a strong binary category and a detailed dimension (time of day / age / distribution etc.)

How to create tornado chart in Excel?

Just open Excel in your laptop, load your data, go outdoors in Oklahoma (if you live outside Midwestern states then catch a flight to nearest one) and wait for a tornado.

Jokes aside, to tornado charts are nothing but cleverly formatted bar charts. Let me demonstrate how to make them from a sample data of London’s actual & projected population numbers.

Watch this video tutorial or read the instructions below to create this chart in Excel.

Step 1: Calculate necessary numbers for the tornado chart

This depends on your data. For the London population data-set that I am using, we need a summary like this.

How to Create a Tornado Screw Pattern in Microsoft ExcelI am using a pivot table to make the calculations.

Once you have the numbers by age and gender, we need to turn one of the gender values to negative.

To do this, just create a copy of the calculations, paste as linked values. Turn one gender values to negative by using – (minus) sign in the front.

We get this sort of table.

How to Create a Tornado Screw Pattern in Microsoft ExcelUse Paste Links option to get a copy of data. Turn one gender values -ve

Step 2: Make a stacked bar chart

Select your age by gender (with negative values for one gender) values and insert “stacked bar” chart.

You will get this.

How to Create a Tornado Screw Pattern in Microsoft Excel

Step 3: Format the tornado chart

We are almost done. Just format the chart using below steps.

  1. Set gap between bars to 0 (select any bar, press CTRL+1 to format them and set gap width to 0)
  2. Move vertical axis labels to either low or high position, so that you can read them.
  3. Flip the tornado so you can see age 0 on top and 100 at bottom. To do this, select the vertical axis, go to format and click on “Categories in reverse order” option.
  4. Remove -ve signs from the horizontal axis labels. To do this, select the axis, format and go to “Number” settings. Here, you can tell Excel to omit the -ve sign while displaying labels with special codes. For numbers, you can use the code 0;0;;
    Related: For more on custom formatting codes, see this page.
  5. Move legend to top
  6. Add relevant chart title and captions if necessary.

Here is a time lapse GIF of the formatting steps.

How to Create a Tornado Screw Pattern in Microsoft ExcelFormatting steps for tornado chart

Step 4: Make it interactive

Now that you have a tornado chart, you can easily make it interactive. Just move the slicer (from step 1) closer to the chart and you have an interactive tornado chart in Excel.

How to Create a Tornado Screw Pattern in Microsoft Excel

Download Tornado Chart Excel Template

Click here to download the full tornado chart template. Use it to learn how to make these.

Tornado Chart Alternatives

Histograms and Pareto Analysis

How to Create a Tornado Screw Pattern in Microsoft Excel

When you have more than two categories, then try histogram charts. You can explore distribution of all data or make it interactive (with slicers of course). See this page for details on histograms in Excel.

In-cell bar charts – when you have too many categories

How to Create a Tornado Screw Pattern in Microsoft Excel

Another option is to make a table visualizing everything. But a table of numbers can be dull. So make them visual with in-cell bar charts. Here is a case study of survey results from “how people in various countries spend money?”.

Check out Advanced Charting page for more inspiration.

Do you make tornado charts?

I create tornado charts often, especially when I am exploring demographic trends.

What about you? Do you make tornado charts? If so, how do you make them? Please share your tips and experiences in the comments section.

  • Print
  • Email

The insertion of a Butterfly, which is also known as tornado chart, is going to be done in a simple step-by-step format, without any complications.

But first, you need this kind of data:

How to Create a Tornado Screw Pattern in Microsoft Excel

Click on any part of the data (1), insert (2), and bar chart (3), then stacked bar (4).

Click on design.

Click on Add chart element (1), Legends (2), and Top (3).

Right click on one of the series (1), and Select Data (2).

Click on the arrows to sort the series Padding South, South, Gap, North, and Padding North (1), and press ok (2).

Right click on padding North (1), and click on no fill (2).

Click on no fill.

Note: You should repeat previous steps on Gap, and Padding north series.

Right click on the gap (1), add data labels twice (2 & 3).

Right click on gap (1), and choose Format Data Labels (2).

Check category name (1), and uncheck values (2).

Right click on the x axis (1), and choose format axis (2).

Click labels (1), and choose “none” in the labels position (2).

Click on the line, and press delete.

Click on Padding North, Gap, and Padding South, and then press delete.

Stretch the legends, so both south, and North is showing on their sides.

In conclusion, you can keep working on the chart as you please.

You can download free Butterfly Chart template here.

Chandoo

It’s tornado season. Don’t freak out, I am talking about Excel tornado charts. Use them to visualize age and gender-wise KPIs. Here is a quick demo of interactive tornado chart made in Excel. Watch it and read on to learn how to make your own tornado in a spreadsheet.

How to Create a Tornado Screw Pattern in Microsoft ExcelDemo of tornado chart in Excel – with slicer interactions

When to use Tornado charts?

Tornado charts / population pyramids are very useful in below situations.

  • See population distribution by gender (obviously)
  • Purchase patterns by gender and ages
  • Customer walk-ins by gender and time of day
  • Distribution of time on page by visitor type (customer / prospect)
  • Units purchased by customer types (international vs. domestic)

In general, you can apply this type of charts whenever you have a strong binary category and a detailed dimension (time of day / age / distribution etc.)

How to create tornado chart in Excel?

Just open Excel in your laptop, load your data, go outdoors in Oklahoma (if you live outside Midwestern states then catch a flight to nearest one) and wait for a tornado.

Jokes aside, to tornado charts are nothing but cleverly formatted bar charts. Let me demonstrate how to make them from a sample data of London’s actual & projected population numbers.

Watch this video tutorial or read the instructions below to create this chart in Excel.

Step 1: Calculate necessary numbers for the tornado chart

This depends on your data. For the London population data-set that I am using, we need a summary like this.

How to Create a Tornado Screw Pattern in Microsoft ExcelI am using a pivot table to make the calculations.

Once you have the numbers by age and gender, we need to turn one of the gender values to negative.

To do this, just create a copy of the calculations, paste as linked values. Turn one gender values to negative by using – (minus) sign in the front.

We get this sort of table.

How to Create a Tornado Screw Pattern in Microsoft ExcelUse Paste Links option to get a copy of data. Turn one gender values -ve

Step 2: Make a stacked bar chart

Select your age by gender (with negative values for one gender) values and insert “stacked bar” chart.

You will get this.

How to Create a Tornado Screw Pattern in Microsoft Excel

Step 3: Format the tornado chart

We are almost done. Just format the chart using below steps.

  1. Set gap between bars to 0 (select any bar, press CTRL+1 to format them and set gap width to 0)
  2. Move vertical axis labels to either low or high position, so that you can read them.
  3. Flip the tornado so you can see age 0 on top and 100 at bottom. To do this, select the vertical axis, go to format and click on “Categories in reverse order” option.
  4. Remove -ve signs from the horizontal axis labels. To do this, select the axis, format and go to “Number” settings. Here, you can tell Excel to omit the -ve sign while displaying labels with special codes. For numbers, you can use the code 0;0;;
    Related: For more on custom formatting codes, see this page.
  5. Move legend to top
  6. Add relevant chart title and captions if necessary.

Here is a time lapse GIF of the formatting steps.

How to Create a Tornado Screw Pattern in Microsoft ExcelFormatting steps for tornado chart

Step 4: Make it interactive

Now that you have a tornado chart, you can easily make it interactive. Just move the slicer (from step 1) closer to the chart and you have an interactive tornado chart in Excel.

How to Create a Tornado Screw Pattern in Microsoft Excel

Download Tornado Chart Excel Template

Click here to download the full tornado chart template. Use it to learn how to make these.

Tornado Chart Alternatives

Histograms and Pareto Analysis

How to Create a Tornado Screw Pattern in Microsoft Excel

When you have more than two categories, then try histogram charts. You can explore distribution of all data or make it interactive (with slicers of course). See this page for details on histograms in Excel.

In-cell bar charts – when you have too many categories

How to Create a Tornado Screw Pattern in Microsoft Excel

Another option is to make a table visualizing everything. But a table of numbers can be dull. So make them visual with in-cell bar charts. Here is a case study of survey results from “how people in various countries spend money?”.

Do you make tornado charts?

I create tornado charts often, especially when I am exploring demographic trends.

What about you? Do you make tornado charts? If so, how do you make them? Please share your tips and experiences in the comments section.

Tornado chart in excel is a type of bar chart which is also used to compare data among different types of data or categories, the bars in the tornado chart are horizontal and this chart is basically used to show the impact such as how a condition will impact the result on the outcome.

Tornado Chart in Excel

Excel Tornado chart helps in analyzing the data and decision making process. It is very helpful for sensitivity analysis which shows the behavior of the dependent variables i.e., it shows how one variable is impacted by the other. In other words, it shows how the output will be affected by the input.

This chart is useful in showing the comparison of data between two variables.

It is a Bar chart having the bars represented horizontally. This chart has the bars of two variables facing opposite directions with the base for the both in the middle of the chart which makes it look like a Tornado and so it is named as Tornado Chart. It is also called a Butterfly chart or Funnel Chart in Excel.

How to Create a Tornado Screw Pattern in Microsoft Excel

Examples of Tornado chart in excel

Now let us learn how to make a Tornado chart in excel. The below example shows the comparison of data for the products in two different locations.

Example #1 – Comparison of Two Variables

  • Enter the data set in an excel worksheet with the variable’s name and values.

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Arrange the data set of the first variable in ascending order by sorting it Smallest to Largest
  • Select the data to insert a chart (A1:C7)

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Select the 2-D Stacked Bar Graph from the Charts section in the Insert tab.

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Select the first variable and do right-click to select “Format Data Series” option
  • Select the “Secondary Axis” option in the Format Data series panel

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Select the Secondary Axis in excel chart and do right click to select the “Format Axis” option

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Set the Axis Bounds minimum value under Axis options with the negative value of the maximum number (Both Maximum and Minimum bounds should be same but the minimum value should be negative and Maximum value should be positive)

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Also, check the box for “Values in Reverse Order” under Axis options in the Format Axis panel.

How to Create a Tornado Screw Pattern in Microsoft Excel

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Now select the primary axis and do right click to select the “Format Axis” option

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Set the Axis bounds minimum with negative and maximum with positive values (same as above)

How to Create a Tornado Screw Pattern in Microsoft Excel

Now click on the axis showing the product name (A, B, C…).

Funnel charts show values across multiple stages in a process. For example, you could use a funnel chart to show the number of sales prospects at each stage in a sales pipeline. Typically, the values decrease gradually, allowing the bars to resemble a funnel.

How to Create a Tornado Screw Pattern in Microsoft Excel

Note: This feature is only available in Excel for Windows if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Set up your data like the above example. Use one column for the stages in the process, and one for the values.

Select the data.

Click Insert > Insert Waterfall, Funnel, Stock, Surface or Radar chart > Funnel.

How to Create a Tornado Screw Pattern in Microsoft Excel

Tip: In Excel, use the Design and Format tabs to customize the look of your chart. If you don’t see these tabs, click anywhere in the funnel chart, and then the Chart Tools will appear at the top of the program:

Note: This feature is only available in Excel for Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Set up your data like the above example. Use one column for the stages in the process, and one for the values.

Select the data.

On the ribbon, click the Insert tab, then click (the Waterfall icon) and select Funnel.

Tip: In Excel for Mac, use the Chart Design and Format tabs to customize the look of your chart. If you don’t see these tabs, click anywhere in the funnel chart and the tabs will appear on the ribbon.

Set up your data like the above example. Use one column for the stages in the process, and one for the values.

Select the data.

Click Insert > Chart > Funnel.

Note: This feature is only available in these applications if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Click an empty space in an email message, presentation, or document.

Click Insert > Chart > Funnel.

The funnel chart will appear. And, a small window with example data will appear. Change the numbers to your own.

Tip: At the top of the data window is the Edit Data in Microsoft Excel button . Click this button if you want more room to work with the data.

To add the names of the stages, right-click anywhere in column A, and then click Insert.

Click Entire column, and then click OK.

Type the names of the stages in cells A2, A3, and so on.

At the top of the program window, click the Design tab.

Click Select Data.

Note: In PowerPoint, you may have to move the small data window down to see the Select Data button.

The Select Data Source window will appear. And, dashed lines will appear around the second column of data.

In the data, click and drag to select both columns: the stages and the values. When you’re done, the dashed lines should surround the two columns.

In the Select Data Source window, click OK to close it.

Now close the data window. Or, if you opened Excel to edit your data, close the Excel window.

If you know how to present data in an understandable and effective way, you can deliver your message strongly. For this purpose, one of my favorite things is a PICTOGRAPH. And in Excel, creating a pictograph is quite simple and easy.

How to Create a Tornado Screw Pattern in Microsoft Excel

Today in this post, I’d like to share with you a simple step by step process to create a pictograph in Excel. Here my idea is to present the number of employees from various age groups in a company.

Now without any further ado, let’s get started.

Simple Steps to Create a Pictograph in Excel

We have to use below data for this chart, you can download it from here to follow along. As I said it’s the total employees in a company age wise and we need t present these groups using this chart.

How to Create a Tornado Screw Pattern in Microsoft Excel

But before we start, we need an icon to use in this chart and you can download it from a free icon site.

  • First of all, just select your data to create a bar chart. For this, go to Insert ➜ Charts ➜ 2-D Clustered Bar Chart.

How to Create a Tornado Screw Pattern in Microsoft Excel

  • After that, select the data bars in the chart and go to Right Click ➜ Format Data Series Option

How to Create a Tornado Screw Pattern in Microsoft Excel

  • From here, go to “Fill” section and select “Picture or Texture Fill” option and once you do this, you’ll get an insert picture option.

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Now it’s time to insert the picture and we have three options for this. Click on the “File” option and upload the image you have downloaded.
  • Once you insert the image, you have further three options to show that picture in the chart. [ Select the third option ]
  1. Stretch – A single image will stretch in data bar.
  2. Stack – You will get stacked images in the data bar.
  3. Stack and Scale with – Images will be shown as per data.

Boom! our chart is ready to rock.

How to Create a Tornado Screw Pattern in Microsoft Excel

Note: There is an option to specify unit/picture for the chart. You can use it if the values per bar in your chart are large (more than 20). The thing is that the picture icons will become smaller with more numbers and this option helps in that case.

How to Create a Tornado Screw Pattern in Microsoft Excel

You can also use a different picture for different bars, just like I have used in the below chart.

How to Create a Tornado Screw Pattern in Microsoft Excel

Select each data bar separately and insert a picture one by one for all the bars.

Dynamic Pictograph in Excel

I always love to create interactive charts and for this time I want to convert this PICTOGRAPH into a chart in which we can use data in an dynamic way. Just like below.

How to Create a Tornado Screw Pattern in Microsoft Excel

Look at the below data where we have same company’s employees data, but department wise and we need to create a dynamic chart here which we can operate with option button.

You can download this file from here to follow along and now use below steps to create a dynamic pictograph.

  • First of all, create a different table with two columns, just like I have below.

How to Create a Tornado Screw Pattern in Microsoft Excel

  • Now, insert four option buttons in your worksheet. For this go to Developer Tab ➜ Controls ➜ Insert ➜ Option Button.
  • After that, connect those option buttons with a cell. In my case I’m connecting them with cell A8.
  • Since we need to control data (department wise) with option buttons make sure to name all the buttons according to the department name.
  • From here, insert below formula into the first cell (heading) to last cell of the second column of the new table.

How to Create a Tornado Screw Pattern in Microsoft Excel

  • At this point, we have a dynamic table where we can get data by using option buttons.

How to Create a Tornado Screw Pattern in Microsoft Excel

  • In the end, select this table and insert a bar chart and convert it into a pictograph using the steps you have learned.

Hurry! our dynamic pictograph is here.

How to Create a Tornado Screw Pattern in Microsoft Excel

Sam ple Files

Conclusion

Pictograph is one of my list of the advanced Excel charts. The best thing which I like about this chart is we can use any kind of picture in it, there is no limitation about it.

And you don’t need to worry about the picture you have added, it remains there in the clip-board. I’m sure this chart will help you to present your data in a better way.

And now tell one thing. Have you ever tried to create a chart like this before? Please share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends, I’m sure they will appreciate it.

How to Create a Tornado Screw Pattern in Microsoft Excel

You can add a trendline to a chart in Excel to show the general pattern of data over time. You can also extend trendlines to forecast future data. Excel makes it easy to do all of this.

A trendline (or line of best fit) is a straight or curved line which visualizes the general direction of the values. They’re typically used to show a trend over time.

In this article, we’ll cover how to add different trendlines, format them, and extend them for future data.

How to Create a Tornado Screw Pattern in Microsoft Excel

Add a Trendline

You can add a trendline to an Excel chart in just a few clicks. Let’s add a trendline to a line graph.

Select the chart, click the “Chart Elements” button, and then click the “Trendline” checkbox.

How to Create a Tornado Screw Pattern in Microsoft Excel

This adds the default Linear trendline to the chart.

There are different trendlines available, so it’s a good idea to choose the one that works best with the pattern of your data.

Click the arrow next to the “Trendline” option to use other trendlines, including Exponential or Moving Average.

How to Create a Tornado Screw Pattern in Microsoft Excel

Some of the key trendline types include:

  • Linear: A straight line used to show a steady rate of increase or decrease in values.
  • Exponential: This trendline visualizes an increase or decrease in values at an increasingly higher rate. The line is more curved than a linear trendline.
  • Logarithmic: This type is best used when the data increases or decreases quickly, and then levels out.
  • Moving Average: To smooth out the fluctuations in your data and show a trend more clearly, use this type of trendline. It uses a specified number of data points (two is the default), averages them, and then uses this value as a point in the trendline.

To see the full complement of options, click “More Options.”

How to Create a Tornado Screw Pattern in Microsoft Excel

The Format Trendline pane opens and presents all trendline types and further options. We’ll explore more of these later in this article.

How to Create a Tornado Screw Pattern in Microsoft Excel

Choose the trendline you want to use from the list, and it will be added to your chart.

Add Trendlines to Multiple Data Series

In the first example, the line graph had only one data series, but the following column chart has two.

If you want to apply a trendline to only one of the data series, right-click on the desired item. Next, select “Add Trendline” from the menu.

How to Create a Tornado Screw Pattern in Microsoft Excel

The Format Trendline pane opens so you can select the trendline you want.

In this example, a Moving Average trendline has been added to the charts Tea data series.

How to Create a Tornado Screw Pattern in Microsoft Excel

If you click the “Chart Elements” button to add a trendline without selecting a data series first, Excel asks you to which data series you want to add the trendline.

How to Create a Tornado Screw Pattern in Microsoft Excel

You can add a trendline to multiple data series.

In the following image, a trendline has been added to the Tea and Coffee data series.

How to Create a Tornado Screw Pattern in Microsoft Excel

You can also add different trendlines to the same data series.

In this example, Linear and Moving Average trendlines have been added to the chart.

How to Create a Tornado Screw Pattern in Microsoft Excel

Format Your Trendlines

Trendlines are added as a dashed line and match the color of the data series to which they’re assigned. You might want to format the trendline differently—especially if you have multiple trendlines on a chart.

Open the Format Trendline pane by either double-clicking the trendline you want to format or by right-clicking and selecting “Format Trendline.”

How to Create a Tornado Screw Pattern in Microsoft Excel

Click the Fill & Line category, and then you can select a different line color, width, dash type, and more for your trendline.

In the following example, I changed the color to orange, so it’s different from the column color. I also increased the width to 2 pts and changed the dash type.

How to Create a Tornado Screw Pattern in Microsoft Excel

Extend a Trendline to Forecast Future Values

A very cool feature of trendlines in Excel is the option to extend them into the future. This gives us an idea of what future values might be based on the current data trend.

From the Format Trendline pane, click the Trendline Options category, and then type a value in the “Forward” box under “Forecast.”

How to Create a Tornado Screw Pattern in Microsoft Excel

Display the R-Squared Value

The R-squared value is a number that indicates how well your trendline corresponds to your data. The closer the R-squared value is to 1, the better the fit of the trendline.

From the Format Trendline pane, click the “Trendline Options” category, and then check the “Display R-squared value on chart” checkbox.

How to Create a Tornado Screw Pattern in Microsoft Excel

A value of 0.81 is shown. This is a reasonable fit, as a value over 0.75 is generally considered a decent one—the closer to 1, the better.

If the R-squared value is low, you can try other trendline types to see if they’re a better fit for your data.

An Excel macro is an action or a set of actions that you can record, give a name, save and run as many times as you want and whenever you want. When you create a macro, you are recording your mouse clicks and keystrokes. When you run a saved macro, the recorded mouse clicks and keystrokes will be executed in the same sequence as they are recorded.

Macros help you to save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently.

Macro and VBA

You can record and run macros with either Excel commands or from Excel VBA.

VBA stands for Visual Basic for Applications and is a simple programming language that is available through Excel Visual Basic Editor (VBE), which is available from the DEVELOPER tab on the Ribbon. When you record a macro, Excel generates VBA code. If you just want to record a macro and run it, there is no need to learn Excel VBA. However, if you want to modify a macro, then you can do it only by modifying the VBA code in the Excel VBA editor.

You will learn how to record a simple macro and run it with Excel commands in the chapter – Creating a Simple Macro. You will learn more about macros and about creating and / or modifying macros from Excel VBA editor in the later chapters.

Personal Macro Workbook

A macro can be saved in the same workbook from where you recorded it. In that case, you can run the macro from that workbook only and hence you should keep it open. Excel gives you an alternative way to store all your macros. It is the personal macro workbook, where you can save your macros, which enables you to run those macros from any workbook.

You will learn about Personal Macro Workbook in the chapter – Saving all your Macros in a Single Workbook.

Macro Security

Macros will be stored as VBA code in Excel. As with the case of any other code, macro code is also susceptible to malicious code that can run when you open a workbook. This is a threat to your computer. Microsoft provided with the Macro Security facility that helps you in protecting your computer from such macro viruses.

You will learn more about this in the chapter – Macro Security.

Absolute References and Relative References

While recording a macro, you can use either absolute references or relative references for the cells on which you are clicking. Absolute references make your macro run at the same cells where you recorded the macro. On the other hand, relative references make your macro run at the active cell.

You will learn about these in the chapters – Using Absolute References for a Macro and Using Relative References for a Macro.

Macro Code in VBA

You can record and run macros from Excel even if you do not know Excel VBA. However, if you have to modify a recorded macro or create a macro by writing VBA code, you should learn Excel VBA. You can refer to the Excel VBA tutorial in this tutorials library for this

However, you should know how to view the macro code. You can learn how to access VBA editor in Excel and about the different parts of the VBA editor in the chapter – Excel VBA.

You can learn how to view the macro code in Excel VBA editor and you can understand the macro code in the chapter – Understanding Macro Code.

Assigning Macros to Objects

You can assign a macro to an object such as a shape or a graphic or a control. Then, you can run the macro by clicking on that object. You will learn about this in the chapter – Assigning Macros to Objects.

Running Macros

Excel provides several ways to run a macro. You can choose the way you want to run a macro. You will learn about these different possible ways of running a macro in the chapter – Running a Macro.

Creating a Macro Using VBA Editor

If you decide to write the macro code, you can learn it in the chapter – Creating a Macro Using VBA Editor. However, the prerequisite is that you should have Excel VBA knowledge.

Editing a Macro

You can modify macro code in Excel VBA editor. If you want to make extensive changes, you should have Excel VBA knowledge. But, if you want to make only minor changes to the code or if you want to copy the VBA code from a recorded macro to another macro, you can refer to the chapter – Editing a Macro.

You can rename a macro and even delete it. You will learn about this also in the same chapter.

User Forms

A Form is normally used to collect required information. It will be self-explanatory making the task simple. Excel User Forms created from Excel VBA editor serve the same purpose, providing the familiar options such as text boxes, check boxes, radio buttons, list boxes, combo boxes, scroll bars, etc. as controls.

You will learn how to create a User Form and how to use the different controls in the chapter – User Forms.

Debugging Macro Code

At times, a macro may not run as expected. You might have created the macro or you might be using a macro supplied to you by someone. You can debug the macro code just as you debug any other code to uncover the defects and correct them. You will learn about this in the chapter – Debugging Macro Code.

Configuring a Macro to Run on Opening a Workbook

You can make your macro run automatically when you open a workbook. You can do this either by creating an Auto_Run macro or by writing VBA code for workbook open event. You will learn this in the chapter – Configuring a Macro to Run on Opening a Workbook.

by melih · June 17, 2014

Butterfly chart is a very nice visualization technique for comparing two data series side by side. You can easily see how one series do compared to other series for a given data point in one glance.

I am going to use a sample poll data and visualize results with a butterfly chart.

Note: I first saw this chart here. link–> http://www.databison.com/butterfly-chart-excel-chart-with-dual-converging-scales/

Here is our data:

How to Create a Tornado Screw Pattern in Microsoft Excel

This data is not suitable for building a butterfly chart. So we need to make some adjustments in our data structure to make it more chart friendly.

Here is our adjusted data:

How to Create a Tornado Screw Pattern in Microsoft Excel

Columns E, G and I are equal to columns A, B and C as their names suggests. Columns F, H and J are formulated columns. Column H has a hard coded value (350) it is used for creating a gap between data series to place category labels. Columns F and J are used for spacing, so they are formulated as 1000-Yes value or 1000- No value as their names suggests.

Select all your arranged data and insert a 100% Stacked Bar Chart. Here is how it should look like:

How to Create a Tornado Screw Pattern in Microsoft Excel

Note: Because I remember later and made the correction and all screenshots was already taken, I need to tell you to do this here. Access properties screen for vertical axis and check “Categories in Reverse Order” option. This will make categories appear from 1 to 10 instead of 10 to 1.

Now we are going to make some cleaning on the chart. Do the changes shown in picture below:

How to Create a Tornado Screw Pattern in Microsoft Excel

Your chart should look like the one below at this point:

How to Create a Tornado Screw Pattern in Microsoft Excel

Add labels to remaining data series. For Yes (orange) series, select position as “Inside End” and for No (yellow) series, select position as “Inside Base”. Also select Gap series which is in the middle (invisible) and add labels. Change labels for Gap series from “Value” to “Category Name”.

Here is how it should look now:

How to Create a Tornado Screw Pattern in Microsoft Excel

Double-click any data series and set Gap Width to 75%. Change Yes series fill color to green and No series fill color to red. Also type an appropriate chart title (Poll Results for our example).

Your chart is ready to serve:

How to Create a Tornado Screw Pattern in Microsoft Excel

You can download and inspect this chart from the link bellow.

Download Butterfly Chart

You can also find similar visualizations in these posts:

Feel free to leave a comment below for ant questions or suggestions.

This Excel tutorial explains how to use the Excel FOR. NEXT statement to create a FOR loop in VBA with syntax and examples.

Description

The Microsoft Excel FOR. NEXT statement is used to create a FOR loop so that you can execute VBA code a fixed number of times.

The FOR. NEXT statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

If you want to follow along with this tutorial, download the example spreadsheet.

Syntax

The syntax to create a FOR Loop using the FOR. NEXT statement in Microsoft Excel is:

Parameters or Arguments

Returns

The FOR. NEXT statement creates a FOR loop in VBA.

  • See also the WHILE. WEND statement to create a WHILE loop in VBA.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • VBA function (VBA)

Example (as VBA Function)

The FOR. NEXT statement can only be used in VBA code in Microsoft Excel.

Let’s look at how to create a FOR loop in Microsoft Excel, starting with a single loop, double loop, and triple loop, and then exploring how to change the value used to increment the counter each pass through the loop.

Single Loop

The simplest implementation of the FOR loop is to use the FOR. NEXT statement to create a single loop. This will allow you to repeat VBA code a fixed number of times.

In this example, the FOR loop is controlled by the LCounter variable. It would loop 5 times, starting at 1 and ending at 5. Each time within the loop, it would display a message box with the value of the LCounter variable. This code would display 5 message boxes with the following values: 1, 2, 3, 4, and 5.

Single Loop – Changing Increment

By default, the FOR loop will increment its loop counter by 1, but this can be customized. You can use STEP increment to change the value used to increment the counter. The FOR loop can be increment can be either positive or negative values.

Positive Increment

Let’s first look at an example of how to increment the counter of a FOR loop by a positive value.

In this example, we’ve used Step 2 in the FOR loop to change the increment to 2. What this means is that the FOR loop would start at 1, increment by 2, and end at 9. The code would display 5 message boxes with the following values: 1, 3, 5, 7, and 9.

Negative Increment

Now, let’s look at how to increment the counter of a FOR loop by a negative value.

When you increment by a negative value, you need the starting number to be the higher value and the ending number to be the lower value, since the FOR loop will be counting down. So in this example, the FOR loop will start at 50, increment by -5, and end at 30. The code would display 5 message boxes with the following values: 50, 45, 40, 35, and 30.

Double Loop

Next, let’s look at an example of how to create a double FOR loop in Microsoft Excel.

Here we have 2 FOR loops. The outer FOR loop is controlled by the LCounter1 variable. The inner FOR loop is controlled by the LCounter2 variable.

In this example, the outer FOR loop would loop 4 times (starting at 1 and ending at 4) and the inner FOR loop would loop 2 times (starting at 8 and ending at 9). Within the inner loop, the code would display a message box each time with the value of the LCounter1LCounter2. So in this example, 8 message boxes would be displayed with the following values: 1-8, 1-9, 2-8, 2-9, 3-8, 3-9, 4-8, and 4-9.

Triple Loop

Next, let’s look at an example of how to create a triple FOR loop in Microsoft Excel.

Here we have 3 FOR loops. The outer-most FOR loop is controlled by the LCounter1 variable. The next FOR loop is controlled by the LCounter2 variable. The inner-most FOR loop is controlled by the LCounter3 variable.

In this example, the outer-most FOR loop would loop 2 times (starting at 1 and ending at 2) , the next FOR loop would loop 2 times (starting at 5 and ending at 6), and the inner-most FOR loop would loop 2 times (starting at 7 and ending at 8).

Within the inner-most loop, the code would display a message box each time with the value of the LCounter1LCounter2LCounter3. This code would display 8 message boxes with the following values: 1-5-7, 1-5-8, 1-6-7, 1-6-8, 2-5-7, 2-5-8, 2-6-7, and 2-6-8.

Example#1 from Video

In the first video example, we are going to use the For. Next statement to loop through the products in column A and update the appropriate application type in column B.

Example#2 from Video

In the second video example, we have a list of participants in column A and we’ll use two FOR Loops to assign each of the participants to either Team A or Team B (alternating between the two).

How to Create a Tornado Screw Pattern in Microsoft ExcelMacros are little programs that run within Excel and help automate common repetitive tasks. Macros are one of Excel’s most powerful, yet underutilized feature. Using macros, you can save hours and boost productivity manifold.

In this blog post, we will learn how to create an ultra-simple macro in Excel 2013 to introduce you to the concept. For a more detailed overview of advanced Excel functions, including creating complicated macros, try the comprehensive Excel training course for beginners and intermediate users.

What is a Macro?

A macro can be defined as the recording of a series of tasks. It’s the simplest form of automation – show a software program the steps you follow to get something done, and the software will follow along. When used right, macros can save you hours by automating simple, repetitive tasks.

Marcos in Excel are written in Excel VBA (Visual Basic for Applications). This is a version of Visual Basic (a prominent Microsoft programming language) developed specifically for use in Office-like applications. Creating simple macros – copy a formula from one cell to another, for instance – is fairly easy. Complicated macros require a bit more work. You’ll need to be quite intimate with Excel VBA to automate the more complex tasks in your workflow. You can learn more about these in this advanced Excel 2010 course.

For this tutorial, we will create a very simple macro to get you up to speed with Excel programming and automation.

Step 1: Activate the Developer Toolbar

To write macros, you will need access to the Developer toolbar in the top ribbon. This is hidden by default. To activate it, go to File -> Options -> Customize Ribbon and check the box next to ‘Developer’.

Press OK. You should now see the Developer toolbar in the ribbon.

Step 2: Enable Macros

Macros are disabled by default in Excel. This is to protect you from malicious macros running automatically in downloaded Excel files.

To use macros, you will have to first enable them from the Trust Center. To do this, go to File -> Options -> Trust Center. Click on the ‘Trust Center Settings’ button in this menu.

In this new window, go to ‘Macro Settings’ and select the ‘Enable all macros’ radio button.

Click OK. You can now start using macros in your Excel spreadsheets.

Step 3: Prepare the Spreadsheet

In this tutorial, we will create a very simple macro to change the color, size and boldness of text.

In your spreadsheet, type in some text in any of the cells. We will later record a macro to change the formatting of this text.

Now that our spreadsheet is ready, we can start creating the macro.

Step 4: Create a New Macro

In the ‘Developer’ tab, click on ‘Record Macro’

A pop-up window will ask you to give a name, storage location, shortcut and description for the macro.

Type in whatever you like over here. It helps to be descriptive with your macro titles. You might end up using dozens of macros in a large spreadsheet. Being able to quickly find individual macros by name will save you hours of frustration later. It’s also a good practice to add a description to remind you what the macro actually does.

Two important considerations here:

Shortcut Key: The shortcut key gives you quick access to the macro. Using a custom shortcut is highly recommended – you are using macros to save time, after all. A lot of default Excel functions are locked in to use the CTRL + Letter/Number shortcut format. To avoid conflicts with existing shortcuts, add not at least two keys to the shortcut – something like CTRL + SHIFT + A, or CTRL + A + 1

Store Macro in: In this drop down menu, you can decide where to save the macro. If you choose ‘This Workbook’, the macro will be available for use in the existing workbook only. Choosing ‘New Workbook’ will, as you guessed, save the macro in a new workbook. The most powerful option here is to save the macro in ‘Personal Macro Workbook’. Macros stored in this workbook are available across all your workbooks. Think of it as a central repository for every macro that you create. The actual macro workbook file itself will be hidden the Windows AppData folder, but it will load up automatically when you start Excel, giving you complete access to all macros.

For the purpose of this tutorial, we will give our macro a shortcut of CTRL + SHIFT + A and store it within the same workbook.

Step 5: Record the Macro

Once you’ve entered all the require details, click OK. Your macro is now being recorded.

Select the cell where you entered your text. Switch to the ‘Home’ tab and change its color to red, font size to 18, and font weight to ‘bold’.

When you’re done, click on the ‘Stop Recording’ button in the Developer tab, or hit the small square block at the bottom of the spreadsheet next to ‘Ready’.

You have now successfully recorded an Excel macro!

Step 6: Test the Macro

Now that you’ve recorded your first macro, it’s time to see it live.

Add a few more blocks of text to your spreadsheet, one cell at a time. You might have to change the default formatting back to black font, 11 point font size and normal text.

Once you’ve added the text, simply select any cell and press the shortcut for your macro (CTRL + SHIFT + A). The formatting will immediately change to 18 pt. bold font in red color. You can apply this to each cell at a time, or select multiple cells together.

The formatting changes that would otherwise take you multiple clicks now take just one keystroke.

This is one of the most basic macros you can create. As you can imagine, complex macros can be created to perform very complicated tasks. You can learn more about creating such macros with Excel VBA programming in this Excel training course for advanced topics.

Was this tutorial helpful? We’d love to learn your thoughts and opinions! Share them with us in the comments below.

You can use most Microsoft Excel worksheet functions in your Visual Basic statements. For a list of the worksheet functions you can use, see List of worksheet functions available to Visual Basic.

Some worksheet functions are not useful in Visual Basic. For example, the Concatenate function is not needed because in Visual Basic you can use the & operator to join multiple text values.

Calling a worksheet function from Visual Basic

In Visual Basic, the Excel worksheet functions are available through the WorksheetFunction object.

The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1. Another variable, answer , is assigned the result of applying the Min function to myRange . Finally, the value of answer is displayed in a message box.

If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. For example, you can use the Match worksheet function to search a range of cells. In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). However, in a Visual Basic procedure, you would specify a Range object to get the same result.

Visual Basic functions do not use the WorksheetFunction qualifier. A function may have the same name as a Microsoft Excel function and yet work differently. For example, Application.WorksheetFunction.Log and Log will return different values.

Inserting a worksheet function into a cell

To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook.

Example

This example uses the worksheet function Pmt to calculate a home mortgage loan payment. Notice that this example uses the InputBox method instead of the InputBox function so that the method can perform type checking. The Static statements cause Visual Basic to retain the values of the three variables; these are displayed as default values the next time you run the program.

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

You can add emphasis to selected cells in an Excel 2007 worksheet by changing the fill color or applying a pattern or gradient effect to the cells. If you’re using a black-and-white printer, restrict your color choices to light gray in the color palette and use a simple pattern for cells that contain text so that the text remains legible.

Applying a fill color

To choose a new fill color for a cell selection, follow these steps:

Click the Fill Color button’s drop-down menu in the Font group on the Home tab.

The Fill Color palette appears.

How to Create a Tornado Screw Pattern in Microsoft Excel

Select the color you want to use from the drop-down palette.

Excel’s Live Preview lets you see what the cell selection looks like in a particular fill color when you move the mouse pointer over the color swatches before you click the desired color.

Adding patterns to cells

Follow these steps to choose a pattern for a cell selection:

Click the Font dialog box launcher on the Home tab (or press Ctrl+1).

The Font dialog box launcher is that little button in the bottom-right corner of the Font group. The Format Cells dialog box appears.

Click the Fill tab.

How to Create a Tornado Screw Pattern in Microsoft Excel

Click a pattern swatch from the Pattern Style button’s drop-down menu.

Click a pattern color from the Pattern Color button’s drop-down palette.

The Sample box displays the selected pattern and color.

(Optional) To add a fill color to the background of the pattern, click its color swatch in the Background Color section.

Applying a gradient effect

If you select multiple cells, the gradient you choose will be applied to each chosen cell individually instead of creating one gradient over multiple cells.

Press Ctrl+1 to open the Format Cells dialog box and then click the Fill tab.

Click the Fill Effects button.

The Fill Effects dialog box appears, with controls that enable you to define the two colors to use as well as the shading style and variant.

How to Create a Tornado Screw Pattern in Microsoft Excel

Select the two colors you want to use in the Colors section.

Select one of the Shading Styles radio buttons to choose what type of gradient pattern you want to use; then click the variant that you want to use.

The Sample box displays the current selections.

Click OK two times to close both dialog boxes.

You can remove fill colors, patterns, and gradients assigned to a cell selection by clicking the No Fill option on the Fill Color button’s drop-down menu on the Home tab.

Despite all the functions provided by Excel, you may need one that you just don’t see offered. Excel lets you create your own functions by using VBA programming code; your functions show up in the Insert Function dialog box.

Writing VBA code is not for everyone. But nonetheless, here is a short-and-sweet example. If you can conquer this, you may want to find out more about programming VBA. Who knows — maybe one day you’ll be churning out sophisticated functions of your own! Make sure you are working in a macro-enabled workbook (one of the Excel file types).

Follow along to create custom functions:

This gets you to the Visual Basic Editor, where VBA is written.

You can also click the Visual Basic button on the Developer tab of the Ribbon. The Developer tab is visible only if the Developer checkbox is checked on the Customize Ribbon tab of the Excel Options dialog box.

Choose Insert→Module in the editor.

You have an empty code module sitting in front of you. Now it’s time to create your very own function!

Type this programming code, shown in the following figure:

How to Create a Tornado Screw Pattern in Microsoft Excel

Save the function.

Macros and VBA programming can be saved only in a macro-enabled workbook.

After you type the first line and press Enter, the last one appears automatically. This example function adds two numbers, and the word Public lists the function in the Insert Function dialog box. You may have to find the Excel workbook on the Windows taskbar because the Visual Basic Editor runs as a separate program. Or press Alt+ F11 to toggle back to the Workbook.

Return to Excel.

Click the Insert Function button on the Formulas tab to display the Insert Function dialog box.

How to Create a Tornado Screw Pattern in Microsoft Excel

The Function Arguments dialog box opens, ready to receive the arguments. Isn’t this incredible? It’s as though you are creating an extension to Excel, and in essence, you are.

How to Create a Tornado Screw Pattern in Microsoft Excel

This is a very basic example of what you can do by writing your own function. The possibilities are endless, but of course, you need to know how to program VBA.

Macro-enabled workbooks have the file extension .xlsm .

Get started with these tips for beginners

  • Tweet
  • Share
  • Email

These Excel tutorials for beginners include screenshots and examples with detailed step-by-step instructions. Follow the links below to learn everything you need to get up and running with Microsoft’s popular spreadsheet software.

This article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac, and Excel for Android.

Understand the Excel Screen Elements

How to Create a Tornado Screw Pattern in Microsoft Excel

Understand the Basic Excel Screen Elements covers the main elements of an Excel worksheet. These elements include:

  • Cells and active cells
  • Add sheet icon
  • Column letters
  • Row numbers
  • Status bar
  • Formula bar
  • Name box
  • Ribbon and ribbon tabs
  • File tab

Explore a Basic Excel Spreadsheet

How to Create a Tornado Screw Pattern in Microsoft Excel

Excel Step by Step Basic Tutorial covers the basics of creating and formatting a basic spreadsheet in Excel. You’ll learn how to:

  • Enter data
  • Create simple formulas
  • Define a named range
  • Copy formulas with the fill handle
  • Apply number formatting
  • Add cell formatting

Create Formulas With Excel Math

How to Create a Tornado Screw Pattern in Microsoft Excel

To learn how to add, subtract, multiply, and divide in Excel, see How to Use Basic Math Formulas Like Addition and Subtraction in Excel. This tutorial also covers exponents and changing the order of operations in formulas. Each topic includes a step-by-step example of how to create a formula that carries out one or more of the four basic math operations in Excel.

Add Numbers With the SUM Function

How to Create a Tornado Screw Pattern in Microsoft Excel

Adding rows and columns of numbers is one of the most common operations in Excel. To make this job easier, use the SUM function. Quickly Sum Columns or Rows of Numbers in Excel shows you how to:

  • Understand the SUM function syntax and arguments
  • Enter the SUM function
  • Add numbers quickly with AutoSUM
  • Use the SUM function dialog box

Move or Copy Data

How to Create a Tornado Screw Pattern in Microsoft Excel

When you want to duplicate or move data to a new location, see Shortcut Keys to Cut, Copy, and Paste Data in Excel. It shows you how to:

  • Copy data
  • Paste data with the clipboard
  • Copy and paste using shortcut keys
  • Copy data using the context menu
  • Copy data using menu options on the Home tab
  • Move data with shortcut keys
  • Move data with the context menu and using the Home tab

Add and Remove Columns and Rows

How to Create a Tornado Screw Pattern in Microsoft Excel

Need to adjust the layout of your data? How to Add and Delete Rows and Columns in Excel explains how to expand or shrink the work area as needed. You’ll learn the best ways to add or remove singular or multiple columns and rows using a keyboard shortcut or the context menu.

Hide and Unhide Columns and Rows

How to Create a Tornado Screw Pattern in Microsoft Excel

How to Hide and Unhide Columns, Rows, and Cells in Excel teaches you how to hide sections of the worksheet to make it easier to focus on important data. It’s easy to bring them back when you need to see the hidden data again.

Enter the Date

How to Create a Tornado Screw Pattern in Microsoft Excel

To learn how to use a simple keyboard shortcut to set the date and time, see Use Shortcut Keys to Add the Current Date/Time in Excel. If you prefer to have the date automatically update every time the worksheet is opened, see Use Today’s Date within Worksheet Calculations in Excel.

Enter Data in Excel

How to Create a Tornado Screw Pattern in Microsoft Excel

Dos and Dont’s of Entering Data in Excel covers best practices for data entry and shows you how to:

  • Plan the worksheet
  • Lay out the data
  • Enter headings and data units
  • Protect worksheet formulas
  • Use cell references in formulas
  • Sort data

Build a Column Chart

How to Create a Tornado Screw Pattern in Microsoft Excel

How to Use Charts and Graphs in Excel explains how to use bar graphs to show comparisons between items of data. Each column in the chart represents a different data value from the worksheet.

Create a Line Graph

How to Create a Tornado Screw Pattern in Microsoft Excel

How to Make and Format a Line Graph in Excel in 5 Steps shows you how to track trends over time. Each line in the graph shows the changes in the value for one data value from the worksheet.

Visualize Data With a Pie Chart

How to Create a Tornado Screw Pattern in Microsoft Excel

Understanding Excel Chart Data Series, Data Points, and Data Labels covers how to use pie charts to visualize percentages. A single data series is plotted and each slice of the pie represents a single data value from the worksheet.

tech journalist, PCWorld |

Today’s Best Tech Deals

Picked by PCWorld’s Editors

Top Deals On Great Products

Picked by Techconnect’s Editors

Creating tables in Microsoft Word used to be so annoying that most people just did it in Excel, then imported it into Word. It’s worth giving Word 2013’s table tools a try, though, because the process is easier, and there are some new graphical options.

Seven ways to create tables

Microsoft now provides five different methods for creating tables: the Graphic Grid, Insert Table, Draw Table, insert a new or existing Excel Spreadsheet table, and Quick Tables, plus an option for converting existing text into a table. To start, open a blank Word document from the Home/New page. Position your cursor in the document where you want the table inserted.

Graphic Grid/Select Table from Graph

Under the Insert tab, click the Table button. The Insert Table dialog box will open, showing a basic grid pattern as well as traditional menu options below it. Place your cursor on the first cell in the grid and slide it down and over until you highlight (for this example) four columns and five rows, then click once.

Notice that once the table is created, a new option called Table Tools appears on the Ribbon bar with two new tabs: Design and Layout. See the Layout and Design section below for details regarding these options.

Create a new table using the graphical grid.

Insert Table

Click Insert > Tables > Insert Table from the dropdown menu. In the Insert Table dialog box, enter the number of columns and rows you want in this table (four columns and five rows). In the AutoFit Behavior panel, select Auto, or click the down arrow to choose a specific size. You can also choose AutoFit to Contents (produces narrow columns that expand as you add data) or AutoFit to Window (expands the table to fit the document size). Check the Remember Dimensions for New Tables box if you want the sizes you’re entering now to become your defaults for future tables.

Create a new table using Insert Table.

Draw Table

Click Insert> Tables > Draw Table. The cursor turns into a pencil, which you drag down and across to draw a box. Don’t worry about the exact dimensions; you can modify it any time.

Once the box is created, position the cursor inside the box and draw lines over and down for the columns and rows (one at a time). Don’t worry about crooked lines, either—Word straightens them as you draw.

To add or remove columns and/or rows later, click anywhere inside the table, then select the Design tab under Table Tools. Click the Draw Table button to add or continue drawing lines with your pencil cursor, or click the Eraser button to remove lines with the eraser cursor. To remove a line, just touch the line with the eraser cursor, and the line disappears.

Create a new table using Draw Table.

Excel Spreadsheet (create In Word)

Click Insert > Tables > Excel Spreadsheet. An Excel spreadsheet inserts at your cursor location. You can continue using Excel and its menus and commands, but after you enter your data it converts to a non-editable graphic.

If you want to add, delete, or modify the spreadsheet, right-click anywhere inside the worksheet graphic, select Worksheet Object from the dropdown menu, then click Edit. The original spreadsheet reappears for editing. Notice the top menu has changed to an Excel menu for edits.

Also from the Worksheet Object dropdown menu, you can click Open to open the spreadsheet in Excel, so you can manipulate it in that program. Or click Convert to view a Windows dialog box that lists file-conversion options.

Create a new table using Excel Spreadsheet.

Excel Spreadsheet (copy and paste existing worksheet)

In the old days, Excel spreadsheets had to be imported into Word. Now you can just copy and paste. Open Excel, highlight the spreadsheet, and copy it. Then open Word, position your cursor at the desired location, and select Paste > Keep Source Formatting.

The other options on the Paste dialog menu are Merge Formatting, which changes the text format to match the file into which you pasted the spreadsheet, and Keep Text Only, which pastes the text without the Excel grid, meaning you will likely have to realign your columns with tabs.

Copy and paste an existing table from Excel.

Quick Tables

Quick Tables are Word’s table templates. In addition to the nine templates provided, you can create your own designs and save them to the Quick Tables Gallery to use later. Click Insert > Tables > Quick Tables. Select a table template from the Quick Tables menu, then modify it to fit your project.

Create a new table using Quick Tables.

Convert Text to Table

The table tools can also make lists a lot easier to customize and even reorganize later. For our example, we’ll turn a classic contact list into a table, using a list of names—first, middle, last—plus the city, state, region, and profession of each person on the list.

For eons, people have used tabs to separate the fields, adding a tab or two to accommodate longer strings of data. But if you do this, when you convert the table to text, it misplaces all the data.

With the Convert Text to Table feature, you can separate the fields (Name, City, State, etc.) with paragraphs, tabs, commas, or other separator character, but use only one separator between each field.

Convert an existing block of text to a table.

How to Create a Tornado Screw Pattern in Microsoft Excel

While most see Microsoft Excel as endless blank rows and columns, others view it as a platform to bring their artwork to life.

Many have managed to push the limits of the spreadsheet-making software that most of us try to avoid at all costs. From landscape masterpieces to recreations of our favorite video game characters, Excel has been unexpectedly used as a drawing tool to create some of the coolest illustrations.

Below, we’ve put together 10 incredible works of art made in Microsoft Excel.

1. Japanese art by Tatsuo Horiuchi

How to Create a Tornado Screw Pattern in Microsoft Excel

After watching his co-workers use Excel, 74-year-old Japanese artist Tatsuo Horiuchi began to experiment with the software. He mastered how to use the spreadsheet-maker’s autofunction feature, and has designed beautiful works of traditional Japanese art.

When he entered and won an Excel autoshape contest in 2006, his work received attention and made its way around the web.

To see more of Horiuchi’s work, visit his website here. If you can’t read Japanese, you may want to use a third-party translator service or your browser’s translator tool to navigate his website.

2. Mona Lisa

How to Create a Tornado Screw Pattern in Microsoft Excel

The world’s most famous painting by Leonardo da Vinci still looks impressive in Excel form.

But don’t be so quick to think this took a lot of time and effort. Flickr user Amit Agarwal admitted to using an Excel art software that automatically converts images to pixel art. Many generators like Think Math’s Pixel Spreadsheet will easily let you to turn an image into pixel art in less than a minute.

3. Avatar

How to Create a Tornado Screw Pattern in Microsoft Excel

Don’t let this spreadsheet fool you either. Like Mona Lisa above, this scene from the science-fiction movie Avatar was generated with an Excel art generator. But it still gets cool points.

4. Drawings by Danielle Aubert

How to Create a Tornado Screw Pattern in Microsoft Excel

The designs above were published in artist Danielle Aubert’s book 16 Months Worth of Drawing Exercises in Microsoft Excel. Aubert says her “drawings were created by changing cell preferences for background color, fill pattern and border styles, and from time to time inserting ‘comment’ boxes and letters or words.”

To see more of Aubert’s work, visit her website here.

5. Nyan Cat

How to Create a Tornado Screw Pattern in Microsoft Excel

Nyan Cat is a YouTube video, an Internet meme and now an Excel spreadsheet. In the image above, Imgur user Michelleasaurr recreated the famous animated cartoon cat with a cherry Pop-Tart body flying through outer space.

6. Super Mario Bros.

How to Create a Tornado Screw Pattern in Microsoft Excel

Here’s another impressive sketch by Imgur user Michelleasaurr. This scene from Nintendo’s popular Super Mario Bros. video game was created entirely in Excel.

7. President Obama

How to Create a Tornado Screw Pattern in Microsoft Excel

Excel can create beautiful portraits of humans, too. DeviantART user Katak888 from Indonesia used pixel art to depict a highly detailed portrait of President Obama.

8. Batman

How to Create a Tornado Screw Pattern in Microsoft Excel

Everyone’s favorite fictional superhero comes to life in this Excel sketch. DeviantART user Katak888 used the software to recreate Batman swinging on a rope while wearing his iconic cape and mask.

9. Pikachu

How to Create a Tornado Screw Pattern in Microsoft Excel

Pikachu, one of the most famous and lovable Pokémon creatures, is shown in this spreadsheet by Imgur user VagabondGiraffe.

10. Grand Theft Auto

How to Create a Tornado Screw Pattern in Microsoft Excel

Chilean artist Felipe Velásquez, who goes by Shukei, has made a hobby out of Excel art. Using vectors and the autoshape tool, Shukei has created numerous illustrations, and has recorded his process in creating them on his YouTube channel.

The Grand Theft Auto drawing (seen above) took him 12 hours to design, but in this YouTube video, you can watch him draw it in a nine-minute time-lapse.

Summary

This article demonstrates how to automate Microsoft Excel and how to fill a multi-cell range with an array of values. This article also illustrates how to retrieve a multi-cell range as an array by using Automation.

More Information

To fill a multi-cell range without populating cells one at a time, you can set the Value property of a Range object to a two-dimensional array. Likewise, a two-dimensional array of values can be retrieved for multiple cells at once by using the Value property. The following steps demonstrate this process for both setting and retrieving data using two-dimensional arrays.

Build the Automation Client for Microsoft Excel

Start Microsoft Visual Studio .NET.

On the File menu, click New, and then click Project. Select Windows Application from the Visual Basic Project types. By default, Form1 is created.

Add a reference to Microsoft Excel Object Library. To do this, follow these steps:

  1. On the Project menu, click Add Reference.
  2. On the COM tab, locate Microsoft Excel Object Library, and then click Select.

Note Microsoft Office 2007 and Microsoft Office 2003 include Primary Interop Assemblies (PIAs). Microsoft Office XP does not include PIAs, but they can be downloaded.

Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click Yes.

On the View menu, select Toolbox to display the Toolbox. Add two buttons and a check box to Form1.

Set the Name property for the check box to FillWithStrings.

Double-click Button1. The code window for the Form appears.

Add the following to the top of Form1.vb:

In the code window, replace the following code

Return to the design view for Form1, and then double-click Button2.

In the code window, replace the following code

Test the Automation Client

  1. Press F5 to build and to run the sample program.
  2. Click Button1. Microsoft Excel is started with a new workbook, and cells A1:E5 of the first worksheet are populated with numeric data from an array.
  3. Click Button2. The program retrieves the data in cells A1:E5 into a new array and displays the results in a message box.
  4. Select FillWithStrings, and then click Button1 to fill cells A1:E5 with the string data.

References

For additional information about using arrays to set and retrieve Excel data with earlier versions of Visual Studio, click the article numbers below to view the article in the Microsoft Knowledge Base:

247412 INFO: Methods for Transferring Data to Excel from Visual Basic

Get it now with a Microsoft 365 subscription

How your email address is used.

Microsoft will use your email address only for this one-time transaction. Your information won’t be stored.

Enhanced by intelligence for experts and novices

Excel learns your patterns, organizing your data to save you time. Easily create spreadsheets from templates or on your own and use modern formulas to perform calculations.

Get a better picture of your data

New charts and graphs help you present your data in compelling ways, with formatting, sparklines, and tables to better understand your data. Easily create forecasts to predict trends with just a click. 1

Work better together

Make analog spreadsheets digital with a camera click

You now can add data to Excel directly from a photo. Using the Excel app, just take a picture of a printed data table on your Android or iPhone device 4 and automatically convert the picture into a fully editable table in Excel. This new image recognition functionality eliminates the need to manually enter hard copy data.

Money in Excel

Import your financial transactions into Excel so you can view, organize, and get personalized insights on your finances in one place. 3