IMG_0774

5 PowerPoint Professionals You Should be Following

PowerPoint is not an easy skill to master. Behind every great PowerPoint are hours of design, sweat, and tears. Thankfully, there are bright PowerPoint experts in our midst, here to alleviate any undue stress, caused by that last minute slide update before an important client meeting. Whether you need to learn the techniques for planning a presentation or putting together a well-developed slide deck, these are resources you should have in your back pocket. Below are 5 presentation and PowerPoint experts you should be following, if you’re not already.

1. Geetesh Bajaj - Indezine

Created by Geetesh Bajaj, Indezine is a site dedicated to providing the tools and resources any PowerPoint user needs to execute an effective PowerPoint presentation. Reviews, interviews with professionals in the field, as well as add-in recommendations, Indezine provides a roadmap to getting started with and mastering PowerPoint.

2. Cole Nussbaumer – Storytelling with Data

Storytelling with Data is run by Cole Nussbaumer. Her goal? “To rid the world of ineffective graphs, one exploding pie chart at a time.” With a focus on data visualization, Nussbaumer assists individuals and companies in finding the most effective ways of representing their data. Her underlying principle is that the data should tell a clear story, and she provides tips on just how to make that happen.

3. Nancy Duarte – Duarte Design

If you want to know about presentation design, you need to know Nancy Duarte. Passionate about visual storytelling, Duarte has authored several books, one being Slide:ology: the Art and Science of Creating Great Presentations. You may recognize some of her work on Al Gore’s documentary, An Inconvenient Truth. Follow her on Twitter.  Listen to her TED Talk.

4. Sandra Johnson - PowerPoint Wiz

As a Microsoft PowerPoint MVP, Sandra Johnson brings her 20-year marketing background to assist enterprise companies in creating business-worthy presentations. Focusing on presentation as an art form, Sandra helps her clients communicate their message succinctly.

5. Dave Paradi – Think Outside the Slide

Dave Paradi is the author of 7 PowerPoint presentation books, as well as a consultant on giving an effective PowerPoint presentation. His work has been featured in The Wall Street Journal, and he has worked on prominent presentations, including one used to brief President Obama’s cabinet ministers.  His workshops provide ways in which professionals can communicate their message clearly and effectively, and his site is brimming with free resources to get you started.

ncaabracket33

March Madness: Using Excel to Pick the Upset

Bracket Mania

It’s that time again! Time to get out the office pool and place all of your stock in one team to take you to the finals and earn you those much sought after office bragging rights. Last year, to aid in my bracket-making decision, I decided to write a way to be able to generate a randomized bracket for the NCAA college Men’s Basketball Tournament using Excel. This year, I dug a bit more into using an alternative method, the KenPom rating, to see if I could predict a better bracket. You can dig into this stuff endlessly (or so it seems), starting out with the source of the rankings at KenPom.com. KenPom.com is a college basketball statistics site that has been featured on FiveThirtyEight, Mediaite, and The Wall Street Journal for its predictions in game outcomes. Ken Pomeroy, the creator of the site, has statistics dating back to the 2003 season, and his research is used by many college basketball teams to gain a competitive advantage.

The bracket I made this year was built in a couple of different ways. First, I used the seed strategy, where a higher seed historically wins X% of the time. Second, I used KenPom to figure out how the match-ups might work based on a random outcome. This second method does allow for some very volatile outcomes, so use at your own risk!

I am attaching a project that goes into all of the details of generating the brackets, and I have updated it from last year to get the latest attempt to win some bragging rights.

Download the Brackets and Excel Spreadsheets

To see the completed Excel spreadsheet and my 2014 bracket picks, click the links below. May the odds be ever in your favor!

The full Excel file: NCAATournamentBracket
The C# project: TournamentBracket_2014
My tournament bracket: TournamentBracket_2014_Final

Note: To save the bracket, you need to turn off the auto-calculation feature. This is due to Excel always updating the RAND() output on each change. I recommend changing it to Manual Calculation, and using F9 to run it when you want new values. Don’t forget, this may eventually run through every possible outcome; let me know if you end up winning anything from this!

Predictions

And the final prediction for this year is: Arizona wins it all.

Yes, the likelihood of me being correct is small, but given the current odds (6 / 1), I am feeling pretty good about it. Which does, of course, guarantee that this will be the incorrect bet.

Editor’s Note: Chad (the author of this post) hails from Arizona, so fair warning, there may be some home-state bias in this post.

[Press Release] SoftArtisans Expands its Product Offerings with the Addition of PowerPointWriter

FOR IMMEDIATE RELEASE

SoftArtisans Expands its Product Offerings with the Addition of PowerPointWriter

SoftArtisans releases OfficeWriter version 9 and introduces a new tool named PowerPointWriter to their .NET API. PowerPointWriter is a template-based approach to generate dynamic PowerPoint presentations.

WATERTOWN, MA – February 26, 2014 - SoftArtisans, a leading developer of Office-based reporting software, announces the release of their newest product version, OfficeWriter version 9. Alongside their OfficeWriter version 9 release, SoftArtisans introduces a new tool called PowerPointWriter, thereby greatly expanding the product portfolio of their enterprise software.

PowerPointWriter is a template-based approach to generating reports. Unlike other tools on the market, PowerPointWriter uses a template model, making it easy for developers to design their reports within PowerPoint and merge them with data using as little as five lines of code. VP of Development, Sam Haddad, comments, “This has been a request from our customers for some time now. One of the things our customers always tell us is that they love our template-based approach to generating Office documents, and wanted the same ability with PowerPoint. So we did just that.” PowerPointWriter takes advantage of the technology behind Excel and Word’s mail merge feature, to make combining customized templates with company data easier.

Along with PowerPointWriter, SoftArtisans adds major developments to their ExcelWriter tool. The most notable of which, is a calculation engine, providing users the ability to calculate the results of formulas (such as SUM, PRODUCT, AVERAGE, VLOOKUP, etc.) in an Excel workbook through the OfficeWriter API. This allows users to view formula-laden Excel spreadsheets across many devices and viewers.

OfficeWriter has long been the trusted software for building Microsoft Excel and Word reports in .NET, without requiring Microsoft Office to be installed on the server. In addition to legacy binary file formats XLS and DOC, OfficeWriter supports OOXML files. With the addition of PowerPointWriter, SoftArtisans has expanded into PowerPoint file formats, including PPTX. As a trusted part of enterprise applications, OfficeWriter is easier to use than the OpenXML SDK, and, unlike automating Office on the server, is scalable and optimized for use in ASP.NET. The new product version is now available for download through their site at www.officewriter.com.

###

About SoftArtisans
SoftArtisans is a leading developer of Microsoft Office format reporting software. A key Microsoft partner, SoftArtisans delivers award-winning products to over 20,000 clients in more than 70 countries. SoftArtisans also offers a wide range of technical services that enable clients in industries ranging from finance to healthcare to government, to maximize their reporting abilities with OfficeWriter. Headquartered in Watertown, MA, SoftArtisans has been honored as one of North America’s 500 fastest-growing technology companies.

Contact:
Elise Kovi
elisek@softartisans.com

hands

Upgrading from OfficeWriter 8.x to 9.0

If you are upgrading from OfficeWriter 8.x to OfficeWriter 9, there are a couple important things to note before you install OfficeWriter 9. Running the OfficeWriter 9.0 automatic installer, remove any previous versions of OfficeWriter on your server. If you intend on having OfficeWriter 8 and OfficeWriter 9 run on the same server, special installation steps will need to be followed. Another issue to consider when upgrading is that several deprecated objects have now been removed from the API.

Running OfficeWriter 8.X to 9.0 on the Same Server

Running the OfficeWriter 9.0 automatic installer will remove any previous OfficeWriter 8 installations on the server.

  1. Any version 8 license keys will be removed from the registry
  2. The Program Files folder will be overwritten by the new version
  3. OfficeWriter integration with SSRS installation will also be overwritten.

You will need to preserve the OfficeWriter 8 license key if you have applications on the server that use OfficeWriter 8.

If you want to run OfficeWriter 8.X and 9. 0 on the same server, please choose one of the two following installation paths before beginning to install OfficeWriter 9.

Option 1 Add v8 License Key after Running v9 Installer

Your first option is to record or backup your OfficeWriter 8 license key before running the OfficeWriter 9 installer. During the installation process, your OfficeWriter 8 license key will be removed from the registry and any applications using this license key will not work. However, once the installation is complete, you can run the License Manager program found in the SoftArtisans program files folder and re-add your OfficeWriter 8 license key there.

  1.  Record your license key
    1. Write down your OfficeWriter 8 license key
      1. Open C:\Program Files\SoftArtisans\OfficeWriter\LicenseManager.exe
      2. Click the dropdown to see the installed keys
      3. Copy your installed OfficeWriter 8 license key
    2. OR Export the OfficeWriter 8 license key in the registry.
      1. Enter regedit.exe to get to the registry
      2. Navigate to HKEY_CLASSES_ROOT\Licenses\SoftArtisans
      3. Go to File->Export and enter a file name. Make sure Export Range is set to HKEY_CLASSES_ROOT\Licenses\SoftArtisans
      4. Save the file to your desktop.
  2. OPTIONAL: Back up the C:\\Program Files\SoftArtisans folder.
  3. Run the OfficeWriter 9 installer
  4. Add your OfficeWriter 8 License Key
    1. If you wrote down the license key:
      1. Run License Manager by going to C:\Program Files\SoftArtisans\OfficeWriter\LicenseManager.exe
      2. Enter your version 8 key in the ”New Key” field
      3. Click “Add/Upgrade”
      4. Hit “OK”.
    2. If you exported the version 8 license keys
      1. Go to the registry
      2. Import the keys by clicking File->Import and selecting the registry keys file on the Desktop.

Option 2 Manual v9 Installation

You can also do a manual installation of OfficeWriter 9 to prevent your version 8 license key from being removed.

  1.  Download the OfficeWriter 9 Installer to your desktop
  2. Unpack the V9 Installation Files without Installing OfficeWriter 9
    1. Open Command Prompt and navigate to the desktop by entering: cd desktop
    2. Enter the command msiexec /a C:\Users\YOURUSERNAME\Desktop\OfficeWriterInstaller-9.0.0-x64.msi /qb TARGETDIR=C:\Users\YOURUSERNAME\Desktop\OW9UNPACKED
    3. This will unpack the installer files into a folder on your desktop called ”OW9UNPACKED”
  3. Copy the SoftArtisans folder inside of OW9UNPACKED
  4. Paste the SoftArtisans folder into C:\\Program Files.
  5. Run the License Manager inside of the OW9UNPACKED folder
    1. Enter your version 9 key in the ”New Key” field
    2. Click “Add/Upgrade”
    3. Hit “OK”.
  6. If you are integrating with SSRs, follow these manual SSRS integration instructions.

API Methods and Properties Removed in Version 9

Some API methods and properties that were deprecated in OfficeWriter 8 have now been completely removed from the OfficeWriter 9 API. For more information, please visit this link.

How to Format Rows or Columns only if they Contain Values

Problem

You want to apply particular styles or formats, but only to rows or columns that actually contain values. Here are some examples of styles and formats that you might want to apply to specific rows or columns:

  • Background/Foreground colors
  • Borders
  • Fonts
  • Text orientation, justification, indentation, orientation, number format
  • Horizontal/vertical alignment
  • Cell locked/unlocked when worksheet is protected

Notes:

Conditional formatting is useful if you wish to apply particular formatting that changes depending on the actual values in the cells. Go to Conditional formatting resources.

A similar technique can be used to format just an area. For specifics, see Format an area instead of rows or columns.

Solution

This solution has several steps:

  1. Define an area that contains the populated cells in order to determine which rows and columns to format.
  2. Create the style to apply.
  3. Loop through the rows or columns in the area and apply the style to each row or column.

Step 1:

Get the populate cells in the worksheet. There are two ways to do this:

A. Worksheet.Populated Cells - 

Use Worksheet.PopulatedCells to return an area of all the cells that are populated starting with the top left cell that contains a value or formatting, down to the bottom right cells. It includes both cells with values/formulas as well as cells that contain formatting (e.g. background color, conditional format etc.)

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open("MyUnformattedWB.xlsx");
Worksheet ws = wb.Worksheets["SheetToFormat"];
 
Area populatedArea = ws.PopulatedCells;

 

B. Use a named range

Add a named range in the template file around the area that will contain the populated cells and then use Worksheet.GetNamedRange(String) or Workbook.GetNamedRange(String) to retrieve the named range. Use Range.Areas to get a handle on the desired area.

This is less expensive than Worksheet.PopulatedCells since ExcelWriter does not need to go through the worksheet to figure out which cells contain values.  There are some limitations to this approach:

  • The number of columns in the named range will not change after data is imported. If there are columns in the populated named range that do not contain data, the named range won’t reflect that.
  • The number of rows in the named range will only change after data is imported if  the named range is wrapped around ExcelTemplate data markers and new rows are inserted.
  • If you are using Worksheet.ImportData, the named range will not update to reflect where the live data is.
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open("MyUnformattedWB.xlsx");
Range myRange = wb.GetNamedRange("AreaWithValues");
 
Area populatedArea = myRange.Areas[0];

Step 2:

Create the desired Style with Workbook.CreateStyle().

For this example, we want to leave the populated cells unlocked, but leave the rest of the cells locked when the worksheet is protected. This is done by setting Style.CellLocked.

Style unlockStyle = wb.CreateStyle();
unlockStyle.CellLocked = false;

Step 3:

Determine the first row/column in the area with Area.FirstColumn or Area.FirstRow. Get the total number of rows/columns in the populated area with Area.RowCount or Area.ColumnCount.

int colStart = populatedArea.FirstColumn;
int totalCol = populatedArea.ColumnCount;

Step 4:

Iterate through the rows/columns with Worksheet.GetColumnProperties(int) or Worksheet.GetRowProperties(int). Call ApplyStyle on the appropriate RowProperties or ColumnProperties with ColumnProperties.ApplyStyle(Style) or RowProperties.ApplyStyle(Style). Applying the style on an entire row or column will consume less memory than applying the styles to individual cells.

for(int i = colStart; i < totalCol; i++)
{
     ws.GetColumnProperties(i).ApplyStyle(unlockStyle);
}
 
//For worksheet protection, we also need to protect the worksheet
ws.Protect("MyPassword");

Additional References:

Applying Formatting to just an Area instead of Rows or Columns

If you want to apply the formatting just to specific cells, you can use Area.ApplyStyle()  or Area.SetStyle() instead of looping through the area to apply the style to each row or column.

IMPORTANT: This is only recommended for small areas.  Area.ApplyStyle and Area.SetStyle apply the style to each cell within the area, which increases the time and memory needed to process the file.  Applying styles on a per-row or per-column basis is the recommended best practice since the style is applied to the RowProperties or ColumnProperties object, instead to each individual cell.

Conditional Formatting

Charts not rendering correctly when viewed in non-Excel applications

Problem

An ExcelWriter-generated file with charts displays correctly when opened in Excel.  When the same file is opened in an application that does not have the full Excel functionality, such as Outlook preview or in a mobile application, the charts are missing data or have the wrong data.

When a workbook is opened in Excel, all of the cell values are updated based on the current state of the file. Excel caches these most recent cell values and writes them out to the file. Many mobile apps or applications with a “preview” functionality where the file is locked from being updated, do not have the ability to recalculate the cell values and rely on those cached values to render the charts. When the cached values are not available, the charts may be rendered incorrectly so they may appear to have no data, wrong data, or may not appear in the file at all.

Below are common scenarios where the workbook cannot be fully updated:

  • Mobile applications
  • Applications with “preview,” such as Outlook’s attachment preview
  • Excel’s protected mode, which is activated when files are downloaded from the internet and opened in Excel before saving to disk

Starting in v8.3, ExcelWriter writes out the cached cell values to integrate better with applications that don’t have the ability to update the workbook. However, there are still some limitations that prevent charts from being rendered correctly. This article describes the limitations and expected behavior of rendering charts in files that were generated in ExcelWriter v8.3 or later. 

If you are experiencing issues

Jump to:

Details

iOS Applications

Charts are missing - Certain charts are not supported by iOS, regardless of whether they are in files generated by Excel, ExcelWriter, or some other application. An example of this is the bubble chart. These charts may not be rendered at all, depending on the application. More information is available in this article: Charts in ExcelWriter files are missing when viewed on iOS devices.

Charts are visible, but incorrect - If the chart is visible, but the data appears to be incorrect, then the chart may have data that contains formulas, formatted numbers, or it may be a PivotChart (see below for more info).

Chart data contains formulas

As of v9.0, ExcelWriter has the ability to calculate formulas with the Workbook.CalculateFormulas method. However, for the initial release, only a limited number of formulas are supported.

If ExcelWriter is unable to evaluate formulas (i.e. the formulas you are using are not yet supported or you are using a version prior to v9.0), or Workbook.CalculateFormulas is not called, then the most up-to-date values will not be available. Instead, if there is a formula in the cell, we will write a “0″ to the cached chart field. Despite not being an accurate value, having the cached value reduces the risk of the chart not being rendered or the the application from crashing due to the missing data.

Chart data contains values with number formats applied

ExcelWriter does not have the ability to render numbers based on number formats (e.g. percentage). In the case that a number has a number format applied, the underlying value will be used for the cached field. For example, 6.5% has actual numerical value 0.065, which is the value that ExcelWriter will write to the cached field. This also applies to dates, which are stored as serial numbers in the file and then Excel uses the number format to render the formatted date. For more information about dates in Excel, please see this article: http://www.cpearson.com/excel/datetime.htm.

PivotCharts

PivotCharts have a different structure than regular charts and depend on the entire PivotCache being refreshed to be updated.  Updating the PivotCache is an operation that must be performed in Excel or an application that has the ability to do so. ExcelWriter does not have the ability to refresh the PivotCache and update the PivotCharts. The file will need to be opened in Excel (not in protected mode) in order to update the PivotCache.

Charts in ExcelWriter files are missing when viewed on iOS devices

Problem

An ExcelWriter-generated file with charts displays correctly when opened in Excel, but when the same file is opened on an iPad or iPhone, the charts are missing from the file. There are two causes for this behavior:

  1. The chart type is not supported by iOS. - Some chart types are not supported in iOS and will never appear when viewed on an iPad or iPhone, regardless of whether the file was created in Excel or ExcelWriter. An example of this is the “bubble chart.” This is an issue with iOS mobile applications – not ExcelWriter.
  2. Charts in files generated by ExcelWriter v8.2 and earlier are not visible when viewed on iOS devices. - These chart types are supported in iOS and appear correctly on iPads or iPhones when the charts were created in Excel. If the file was generated by ExcelWriter, the charts are missing when viewed on iOS mobile devices. This is a known issue and was resolved in OfficeWriter 8.3.

This article describes why the second scenario occurs and the expected behavior when viewing charts created by OfficeWriter 8.3 or later.

Solution

When a workbook is opened in Excel, all of the cell values are updated based on the current state of the file. Excel caches these most recent cell values and writes them out to the file. Many mobile apps or applications with a “preview” functionality where the file is locked from being updated, do not have the ability to recalculate the cell values and rely on those cached values to render the rest of the file, including charts.

For iOS devices specifically, if the cached cell values are not available, most applications will not render the charts at all. ExcelWriter was not writing out cached values for the cells. Starting in OfficeWriter 8.3, ExcelWriter will copy the cell values to the cached field. This allows iOS applications to render the charts.

Limitations of Rendering Charts

In certain cases, the charts will be visible but may not look as expected. This is due to limitations in ExcelWriter’s ability to write out the cached values. For more information, please see Charts in ExcelWriter files not rendering correctly outside of Excel.

v9blog

Announcing PowerPointWriter Beta

Join the beta

PowerPointWriter, the template-based way to merge your data with PowerPoint presentations, has arrived. Take your company data and merge it with PowerPoint to create custom presentations. We’re looking for beta testers to try out this new technology.

What is PowerPointWriter?

  • PowerPointWriter is a template-based approach to your presentations.
  • It allows you to build presentations programmatically with 5 lines of code.
  • No more add-ins. PowerPointWriter is a server-side solution to your custom presentations.

Learn more by joining the beta. You’ll receive a special reward just for signing up. Still curious? Take a quick look at our overview video!

 and 




hands

How Not Using Stack Overflow Solves All of My Problems

[OfficeWriter developer and Stack Overflow user, Seth, lets us in on the thought process and psychology behind Stack Overflow, by delving into one way he uses the online community to answer his programming questions. Anything sound familiar? Or relatable? Do you use Stack Overflow in a similar fashion? Let us know in the comments section.]

stackoverflow_post_seth

Stack Overflow is a question and answer site for professional and ardent programmers. It’s a part of the Stack Exchange network of Q&A sites. Their aim is to assemble a resource of quality answers to any and all questions pertaining to programming.

There are a lot of great things about Stack Overflow:

  • It’s free.
  • The point system incentivizes people to help others.
  • The best answers have the highest visibility (usually).
  • There are experts and legendary programmers answering your questions.
  • It’s almost like a sport or competition for those answering questions.

But my favorite thing about StackOverflow is often overlooked: fear.

I can’t tell you how many times I have been so afraid that my question will get downvoted into oblivion that I end up finding the solution while writing it. (I’ll give you a hint: it’s way more than the actual number of questions I’ve posted.)

You see, posting a question on SO is like a code review to the umpteenth power. You’re opening up, not only your code, but your thoughts, explanations, and questions to be scrutinized and judged by anyone in the world. And when you throw tags on your question, you’re basically begging people who know a lot about the subject to come and take their turn.

Also, an increasing number of companies list jobs on careers.stackoverflow, and even more companies have asked me if I have an SO profile…that’s pretty daunting. Companies may be looking at and analyzing my knowledge (or lack thereof) to determine if I’m worthy of their employment!

Not only are prospective employers trolling about there, but the people on SO are my peers. They may end up being co-workers of mine one day. I don’t want them to think I’m an idiot!

And let’s not forget about ego. I worked really hard to get my measly ~2,000 points. It may not seem like much when compared to the Jon Skeet’s of the world, but I answer a lot of questions and get no points, and the questions I ask are usually really domain-specific and never get upvotes. It took me years to get that many points! Because I worked hard for my points, Continue reading

sparklines_image1

Excel Charts Don’t Follow “Move and Size with Cells” Option

Problem

Excel’s “Move and Size with Cells” option allows you to automatically re-size charts if the cells that contain the chart are added or re-sized.

With ExcelWriter, when the “Move and Size with Cells” option is selected in Excel, charts are not re-sized based on rows that are added from data being imported into data markers using the ExcelTemplate object (or OfficeWriter’s SSRS integration). Charts are re-sized if rows are added explicitly using Worksheet.InsertRow.

Example 

The pictures below show a file that has “Move and Size with Cells” option selected, before and after it has been processed by ExcelWriter.

Template

KB_ChartExample1

Incorrect output - Note that the chart remained the same size as before the data was imported.

ExcelWriter Chart

The correct output should look like the following:

ExcelWriter Chart Continue reading

Blogged