This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Greater Munich Metropolitan Area, Germany
I am helping people to turn data into smart decisions
Interests: Data Analysis, Data Visualization, Strategic Analysis, Financial Analysis, Strategic Planning, Financial Planning, Simulation Models, Operations Research, Optimization Algorithms
Recent Activity
Hana, I just imported a Danish text with special characters like "æ" and "å". The split text tool worked like a charm for me. I do not know which issue you are referring to. Sorry.
1 reply
Albert, as far as I know, Excel does not provide an option to allow data entries on worksheets for selected users only. The only option I see is to write VBA code which unprotects the worksheet (either when the workbook is opened, or the sheet is activated) only if the username equals to one of the users allowed to see and edit the sheet. You would then write another VBA routine (e.g. Workbook_BeforeClose) to protect the sheet again.
1 reply
Mauk, thanks for letting me know. I fixed the link, please try again.
1 reply
Latif, I took the video of La Linea, separated it into 56 images using a video editing software, extracted the pixels from those images and calculated the X and Y coordinates of the relevant pixels (the line). But La Linea was just an example to show a somehow funny illustration of the approach. You can bring in your own data and the storyboard technique will still work.
Toggle Commented Jan 18, 2021 on Dynamic Storyboards in Excel at Clearly and Simply
1 reply
Latif, the data source of the line chart are the columns X Offset and Y Offset in the table on worksheet [data]. The values are calculated by formulas based on the preparing calculations on worksheet [calculations]. The formulas are not too complicated. Please download the workbook and have a look for yourself.
Toggle Commented Jan 18, 2021 on Dynamic Storyboards in Excel at Clearly and Simply
1 reply
Peter, I do not understand why you need sliders sitting on top of each other, but anyway: I just tried 2 stacked sliders based on the code in the workbook posted above and it is working fine for me. You have to make the other sliders invisible and keep only the slider on top of the stack visible (either manually or by VBA code), but I do not face any errors with stacked sliders. Since I cannot reproduce the error, it is hard for me to give any advice. If you want to, you can send me your workbook by email and I will have a look.
1 reply
Luca, sure, this is possible. If it is a one-time effort, you can transfer any data from Excel to Project by simply copying and pasting the data into the tables of Project. If you need to do this on a regular basis, you would have to write some VBA code in Project, which asks the user for an Excel workbook to be imported and then automatically imports the data. I do not have a ready-to-use code snippet for this, but it shouldn’t be too complicated to write.
1 reply
Tom, thanks for your comment. First, you are commenting on a 11-years old blog post here. In the meantime, I have done a lot of improvements and variations and published my results here. You should check out and browse through the category Choropleth Maps of this blog and you will find a lot of additional information how to create filled maps in Excel. As for your specific question: the code is only colouring shapes on a worksheet. The mapping of values to the shapes is done in a cell range named MapNameToShape. If you want to have a different Choropleth Map (like Canada in your case), you need to insert the map as freeform shapes, name the shapes and adjust the mapping of values to shapes. To get you started, have a look here: Build your own Choropleth Maps with Excel and/or here: Create Excel Choropleth Maps from Shape Files and, if you have Excel 365, especially here: Optimized Choropleth Maps in Microsoft Excel I hope this helps.
Toggle Commented Dec 1, 2020 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Ibrahim, here you go: map_yemen_by_governorates
1 reply
Dustin, the following blog post provides Choropleth Maps of Germany by ZIP-codes (PLZ2 and PLZ5) in Excel for free download: Create Excel Choropleth Maps from Shape Files If you need the greater Munich area only, you have to take out all shapes you do not need, adjust the input data range and recreate the shape index. This shouldn’t take too long. I hope this helps.
Toggle Commented Nov 20, 2020 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Andy, please have a look at my reply to Jason’s comment on December 18, 2017 above. I also posted an example workbook to download in my comment.
1 reply
Maria, I do not know of a way to display numbers as fractions in Tableau. The only way I see would be to create a Calculated Field dividing the decimal number into the integer, the nominator and the denominator and concatenating those parts separated by the fraction slash.
1 reply
Julie, the command button is the grey box in cells C1:E2 of the worksheet [Words]. The label of the command button says "Split text from a text file into words".
1 reply
Kallun, many thanks for your comment. I hear you. I think we are two of a kind here. Have a look at the workbooks at your own speed and let me know, if you have any questions.
1 reply
Image
The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020 Continue reading
Posted Sep 3, 2020 at Clearly and Simply
10
Dove, have a look at this workbook: Tooltips on xy scatter charts only first series.xlsm
1 reply
Dove, please have a look at my reply to Ben on March 23, 2017 regarding a chart with more than one data series. My reply also provides a workbook for download.
1 reply
anilayyar, the blurriness of the images comes from formatting them with the predefined picture style "Reflected Bevel, White". If you want to see clearer images on the carousel, you need to stay with the original linked images without any Picture Style Formatting. That being said, this solution is designed to be an interactive navigator. The user is not supposed to really view the charts on the navigator, but to select one and then easily jump to the worksheet with the selected view. Thus, the resolution of the images on the navigator sheet does not have to be perfect. The navigator only indicates what kind of visualizations are offered and provides an easy option to jump to what the user decides to have a closer look at. By the way: I edited your comment by taking out your email-address from the author's name in order to keep spam away from your inbox. I hope I acted in your best interest.
1 reply
Image
A Carousel of Charts as a visual and interactive Navigation Control for Microsoft Excel Workbooks Continue reading
Posted Aug 23, 2020 at Clearly and Simply
Ron, it was great to see your comment. My apologies for this late reply, I have been pretty busy the last few days. I fully agree with you. Solving a TSP in the Euclidean Space (i.e. as the crow flies), isn't of great practical use. This wasn’t my intention, though. I simply wanted to visualize how a Neural Network is working and the TSP is a perfect example for this. Even if it is not "true-to-life" as you wrote with good reason. Your remarks about your work on traffic congestion and geographic detour ratios sound intriguing. I would love to hear more about that.
1 reply
Image
Artificial Intelligence in Microsoft Excel: watch a Neural Network at work while solving a Travelling Salesman Problem Continue reading
Posted Aug 7, 2020 at Clearly and Simply
Hilman, firstly, please note that this post is more than 6 years old and all formulas were made with Tableau version 6 (if I remember that correctly). With version 9, Tableau introduced the function FINDNTH, which returns the nth occurrence of a substring (or character) within a string. FINDNTH inside a LEFT function should work for you.
1 reply
Sam, many thanks for your comments. I am sorry, but I do not remember your solution over at EHA. I will look for it and check it out later today. Regarding your reply to Andy: you are right regarding the dynamic arrays in Excel 365 when you are using them on cell ranges. However, how would that solve the problem described above with the Named Formula in a Chart? You can't simply set the INDEX parameter tab_data[Image] into curly brackets, can you? I am using Excel 365 and I tried, but this does not work for me.
1 reply
Carlos, many thanks for your comment. Using Excel 365's Dynamic Arrays is an interesting alternative I haven't thought of. However, your approach does not really solve the issue, because you still need the two helper columns on the [Data] worksheet to calculate the Dynamic Arrays. The basic idea was to transfer all calculations to Named Formulas in order to get rid of the calculated helper columns.
1 reply
Hui, many thanks for your comment. Valid points and sage advice, but I do not think this is the issue here. The Named Formulas contain the reserved word "Plot", but "myXPlotSimple" works and "myXPlotFull" doesn't. Also, I am referencing to the full workbook name, since the names are defined on workbook level, so this shouldn't be the problem either.
1 reply