Stacked Bar and Indicator Arrow Chart – Tutorial

Share

Facebook
Twitter
LinkedIn

Last week in the Chandoo.org Forums, I was asked could I reproduce the chart below in Excel

SBA01

Which I did.

This post will describe how to tackle this chart step by step.

You can follow along using some sample data: Download Sample File

Data

To produce this chart we are going to use an Excel Stacked Bar chart with two series of data

The first Series will be for the Colored Bars

The second series is for the Arrow and the gap to the left of the arrow

SBA02

The data required is shown in the above table

The Arrow Value is an input and is the value the Arrow will point to

The data is the values for the colored bars

The Arrow is two calculations that setup the Arrow, the 58 is the offset from zero to the left side of the arrow

The 4 is the width of the arrow. That is the arrow will point to 60 = 58 + 4 /2

The Cumulative Data is required for the Legend

Chart

In Excel 2016

Select the range C3:G4 and goto Insert Chart

Select Bar, Stacked Bar

SBA04

In Excel 2010

Select the range C3:G4 and goto Insert Chart

Select Bar, Stacked Bar

sba21

Now with the Chart selected goto the Chart Tools, Design Tab

Click on the Switch Row/Column Tab

sba22

 

Excel All Versions

You should now have a chart like:

SBA05

Bar 1 is the data and Bar 2 will become the arrow

We don’t need the Charts Title, Legend, Grid Lines or Axis, so select each and press Delete

SBA06

Next we will add an arrow

The Arrow will be placed as a Fill in the Upper Orange Bar

Select a Blank Cell eg: I3

Then goto the Insert, Shapes Menu and select an Isosceles Triangle

SBA07

Fill the Arrow with what ever color you want and drag the Handle down so that the arrow points down

SBA08

To insert the arrow, select the Arrow and press Copy (Ctrl+C)

Now select the Chart and click on the Upper Orange Bar, click on it again until it is the only Bar with Handles Showing, the press Paste (Ctrl+V)

SBA09

SBA10

Now select the Upper Blue Bar and set its fill and outline to None

 

Right Click on any Bar and select Format Data Series

Set the Series Overlap to 100% and set the Gap Width to 0%

SBA12

Now click on the chart, just above the Blue Bar

When the Resize Handles appear, drag them to resize the chart so that the gap between the Top and Bottom Bars is none

SBA13

We can now add the labels

Select the Orange Bar and then Select it again until it is the only Bar with Handles

Right Click on it and Add Data Label

SBA14

Now click on the new Data Label and click on it again until the Handles change as shown below

SBA15

Now in the Formula Bar enter =Chandoo.org!$C$5 and apply

Right click on the Data Label and select Format Data Labels

Set the Label Position to Inside Base

SBA16

Now repeat for each of the other Bars

SBA17

and Finally add a Label to the arrow linking it to cell C2. You have to manually drag the Label to above the arrow.

Conclusion

Now that you know how to make a Bar Chart with Indicator Arrow, it should take you less than a minute to copy the bar chart, convert it to a Column chart and reformat it to a Column Chart with arrow as shown below

SBA18

I hope you enjoyed the above tutorial

 

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

41 Responses to “SQL Queries from Excel”

  1. Leonid says:

    I use this method very often.
    I always use =SUBSTITUTE (ColumnWithText,"'","''")
    to be sure that potential apostrophe in text columns are doubled as required in SQL.

  2. Chandoo says:

    @Leonid.. that is a good technique to use substitute to clean up text apostrophes. thanks

  3. Paul G. says:

    Goal:
    Generate update statement in excel where the columns that can be updated are dynamic
    You want the columns which are not updated to keep the same value
    (or not be overwritten with NULL values with the new generated statement)
    the statement can be applied to multiple rows in excel for the same column headers
    (This is why the '$' exist for the column headers that are being set)

    A1 = First_Name
    B1 = Last_Name
    C1 = Middle_Name

    ="
    UPDATE PERSONS "&CHAR(10)&
    " SET 1 = 1 "&CHAR(10)&
    IF(LEN(TRIM($A2))=0,"",", "&$A$1&" = '"&$A2&"'"&CHAR(10))&
    IF(LEN(TRIM($B2))=0,"",", "&$B$1&" = '"&$B2&"'"&CHAR(10))&
    IF(LEN(TRIM($C2))=0,"",", "&$C$1&" = '"&$C2&"'"&CHAR(10))&
    " WHERE name = 'staticordynamicvalue' AND gender = 'staticordynamicvalue'
    "
    Output (if all columns are set):
    UPDATE PERSONS SET 1 = 1,
    First_Name = 'Joe',
    Last_Name = 'ORien',
    Middle_Name = 'Richard'
    WHERE age = 28 AND gender = 'm'

    Output (if only First _Name (A1) is set):
    UPDATE PERSONS SET 1 = 1,
    First_Name = 'Joe'
    WHERE age = 28 AND gender = 'm'

  4. Paul G. says:

    Possibly my post above is confusing without the actual table to look at. I will do the same example with the table used here. Instead of an insert statement I will generate an update statement for the columns, Cust_Name, Phone & E-mail
    where we can generate an update statement for any column individually or together. 🙂 I hope this can help.
    =”
    UPDATE table “&CHAR(10)&
    ” SET 1 = 1 “&CHAR(10)&
    IF(LEN(TRIM($A2))=0,”",”,Cust_Name = ‘”&$B3&”‘”&CHAR(10))&
    IF(LEN(TRIM($B2))=0,”",”, Phone = ‘”&$C3&”‘”&CHAR(10))&
    IF(LEN(TRIM($C2))=0,”",”, E-mail = ‘”&$D3&”‘”&CHAR(10))&
    ” WHERE Cust_Name = ’Bill Gates'

  5. Visa Inde says:

    Thanks, it has been very useful !
    It saved me at least 30 minutes, and time is the most expensive thing in our world...

  6. Kad says:

    Hey Paul,
    What if any of A2, B2, or C2 is a date field?
    The formula above is taking date as string. Any solution?

    • Smitha says:

      Even I faced the same problem. If any of the above columns are date, it is taken  as string. Any work around for this?

  7. Sam Howley says:

    I've found the string concatenation method works well.

    At the risk of sounding spammy I would mention that
    if it's something your are doing regularly it might be worth investigating a tools
    that make it easier, such as QueryCell, an excel add-in I've developed.

    It gives you a right click menu option that will produce and then customize insert statements for the selected region of Excel data.

    Cheers
    Sam

    • Pravin says:

      Hi,
      For inserting the excel data to your SQL table, you can create insert statements in excel file according to your columns.
      then just execute the statements all at once, it will insert the required data to sql server table.
      thanks,

  8. Chetan Patil says:

    I tried to generate t-sql insert queries from the above example
    ="insert into values('" &A2 &"','" & B2& "');"
    but it generates on one record instead of all records from excel sheet.
    I'm using Excel 2003 and the excel sheet contains 922 records.

  9. Mike says:

    Most data bases can generate DDL for any object but not a lot of them allow generation of INSERT statements for the table data.
    The workaround is to make use of ETL Tools for transferring data across servers. However, there exists a need to generate INSERT statements from the tables for porting data.
    Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT..VALUES statements come handy.

    There is a number of scripts available to perform this data transformation task. The problem with those scripts that all of them database specific and they do not work with textiles

    Advanced ETL processor can generate Insert scripts from any data source including text files
    http://www.dbsoftlab.com/generating-insert-statements.html

  10. B.N.Prabhu says:

    Super Aiticle. Thanks for this post.

  11. I used to deal with the same problem, until found this awsome and free tool.
    http://www.xtrategics.com/shapp/String%20Handler.application

    regards,

  12. Archana says:

    Hi ,
    i need a sql query to update a DB in excel 2010..
    i have the query(SQL) for insert in excel as ,
    ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');"

    similarly i need q sql query for update in excel

  13. shana says:

    i want clear formulas only for insert,delete,update,select

  14. Ankit Mahendru says:

    Hi !
    I would like to thank you so much ! This trick saves me a  lot of time. Thank you so much. Really appreciate it !
     
    -Ankit

  15. Richard says:

    You may like to take advantage of this unique tool 'Excel to Database'. 
    (free for 60 days)http://leansoftware.net The Excel-to-Database utility enables you to validate and transfer data from Microsoft Excel or text file to a database table or stored procedure process. Any text data can be pasted into the application, this may be from another Excel sheet or from text files such as CSV format. SQL Server, Access, MySQL, FoxPro .. Application features Some unique features of Excel to Database include: ?Easy to use color coded/traffic light data validation ?Data is validated as soon it is typed or pasted into Excel ?Upload Excel data to a table or stored procedure process ?Allow default values ?Mandatory/must have fields can be specified ?Allow user friendly column names ?Allow excel formula / calculated fields ?Multiple database type support: Microsoft SQL Server, Access, MySQL and others (to be tested) ?Supports Custom SQL scripts, with SQL/Excel merge fields ?Database validation checks ensure you comply with any rules defined within the database ?Multiple Task configuration ?For co-operative use, Tasks can be shared across a network ?Task configuration is password protected http://leansoftware.net 

  16. Manoraj says:

    Its works fine for single record.
    I want to update 1000 records in DB. Can you help me.

  17. Richard says:

    Excel database tasks 2.3 (EDT)
    you can now load directly from any source into Excel, validate and upload to most SQL database platforms including SQL Server with automatic transaction wrapping.
    You can also use EDT as a multi-user application by easily designing your own Edit data tasks and deploying EDT on your users workstations.
    Automatically creates UPDATE/INSERT statements based on the primary key.  Default SQL can be modified as you require.
    Makes the best use if Excel power - formatting, formula, validation, conditional formatting..  without creating any problematic spreadsheets!
    Release details on the blog:
    http://leansoftware.net/forum/en-us/blog.aspx
    Thanks for the interest
    Richard
     
     
     

  18. Usman says:

    Thanks for the valueable information, it really help me alot.
     
    Thanks again.

  19. Laercio says:

    As I do with a field of type date?
    = "UPDATE SET business datetime =" & "'" & A2 & "' WHERE ID =" & B2 & ""
    the date is not 03/10/2012 is 41246. Even putting quotes ...

  20. Elaein says:

    Please show how to do it properly with dates as well as when those dates are empty. Thanks!

  21. mahesh.S says:

    In a separate column make the date to Text using below formula
    =TEXT(C2,"mm/dd/yyyy") Then Refer this text column in your update statement

  22. cjb says:

    Great post saved me a a load of time on a task i had to complete

  23. sql010 says:

    thanks for sharing article... helpful!

  24. Pooja says:

    Thanks 🙂

  25. Hello,

    Nice article.

    I have also created one tool for create table script using excel http://devssolution.com/create-table-in-sql-using-excel/

    Please check it.

    Thanks & Regards,
    Sandeep Bhadauriya

  26. […] Excel formula used – http://chandoo.org/wp/2008/09/22/sql-insert-update-statements-from-csv-files/ […]

  27. HSoomro says:

    If any one can help me out with following.
    I want to know a SQL query of below excel formula:
    =LOOKUP(0,-SEARCH(LEFT(F2,LEN($B$2:$B$100))+0,$B$2:$B$100),$A$2:$A$100)

    Excel data is as below;
    Name Codes
    names1 992
    names2 57
    names3 856
    names4 297
    names5 63

    if there is a number (29756789) then it should search in sql by taking the prefix of number (297) from (29756789) and return the name field (name4).
    Codes can be of two digit or three.

    Thanks

  28. David says:

    Here is a link to an Online automator to convert CSV files to SQL Insert Into statements:

    CSV-to-SQL: http://csv-to-sql.herokuapp.com

  29. Victor R Udeshi says:

    ="INSERT INTO table VALUES (" &A3 &",'" & B3 & "','"&C3&"','" & D3 & "','" & E3 & "'," & F3 & "," & G3 & "," & H3 & ",'" & I3 & "'," & J3 & ");"

    B3 has date data that looks like 9/22/17 but with the formula above b3 is coming out as 43000?

    how do i fix that?

  30. Mr.Shan says:

    I just want to insert the Excel records in Sql table without Visiting SQL.
    basically i m just want to run a command in Excel Only.
    Help Me..plz..?

  31. Danyal Hussain says:

    Hi I have a question maybe you guys have an answer for me

    ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" where B3, C3, D3 refer to above table data.

    the above technique works but is there a way to write it so it takes a range instead of individual columns. because I have an extremely wide table

    ="insert into customers values(B3:D3);" where B3, C3, D3 refer to above table data.

  32. Qadir Bux says:

    Awsome

  33. Bhagwat says:

    Its Great Effort to help everyone who working with excel.

  34. Ed says:

    Thanks for the mini-tutorial on SQL from Excel. Didi it several years ago, but couldn't remember the syntax! All the dialogue was really helpful as well!

  35. Administrasi Bisnis says:

    The formula above is taking date as string. Any solution?

Leave a Reply