Tag Archives: Excel

How to Sort Locked Cells in Protected Worksheets

Problem

When creating workbooks with protected sheets, it is common to want to allow users to get different views of the data by sorting and filtering,  without allowing them to change the data. However, while filtering works fine on locked cells, sorting does not. Even if Sort is enabled in the worksheet protection settings, if a user attempts to sort locked cells when a worksheet is protected, Excel throws the error “the cell or chart you are trying to change is protected and therefore read-only.” There is no obvious way to allow users to sort data on a protected worksheet.

Details

Locked VS. Unlocked Cells in Excel

The purpose of locking a cell is to prevent a user from editing the content of a cell when a worksheet is protected. This means when worksheet protection is turned off, a locked cell is no different from an unlocked cell. By default, all cells in an Excel worksheet are locked.

In order to unlock cells in Excel:

  1. Unprotect the sheet you are working on
  2. Right-click the cell, select “Format Cells”
  3. Select the “Protections” tab
  4. Uncheck the “Locked” checkbox property
  5. Click “OK”

Lock Cells Dialog

Lock Cells Dialog Box

It is also possible to unlock specific ranges of cells in Excel using the Allow Users to Edit Ranges feature in the Review tab. Making ranges editable for ranges of cells makes the cells behave like unlocked cells for the most part (e.g. their content is editable). However, the cell’s official locked/unlocked status does not actually change. See this Microsoft Excel article  for more details about how to unlock ranges of cells.


About Worksheet Protection Properties

When you protect a sheet, Excel allows you to select from 15 different permissions you want to give to all viewers of the worksheet. You can allow users of the worksheet to:

  • Select Locked Cells
  • Select Unlocked Cells
  • Format Cells
  • Format Columns
  • Format Rows
  • Insert Columns
  • Insert Rows
  • Insert Hyperlinks
  • Delete Columns
  • Delete Rows
  • Sort
  • Use Autofilter
  • Use PivotTable Reports
  • Edit Objects
  • Edit Scenarios

Protect Sheet Dialog

properties

How Protection Properties are Affected By Locked Cells

When a cell is locked, not all worksheet protection properties operate as you’d expect. Four of the properties do not work when a cell is locked:

  • AllowSort
  • AllowDeleteColumns
  • AllowDeleteRows
  • AllowInsertHyperlinks

This is because in order to use these features the affected cell’s content must be changed. For example, using “Sort” does not just change the order of how the cells are viewed, it actually changes the values of the cells so that they are sorted. Due to this implementation of “Sort,” this worksheet protection property does not work when the cells are locked.

Solution

There are two approaches we can take to solve this issue:

Solution 1: Using “Allow Users to Edit Ranges” to Allow Locked Cell Sorting (RECOMMENDED)

This solution takes advantage of how allowing users to edit ranges makes locked cells behave like unlocked cells.

Step 1: Make cells editable so that sorting will work.

Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

  1. Select all the cells you would like the user to be able to sort, including their column headings.
  2. Go to the Data tab and click Filter. An arrow should appear next to each column header.
  3. Go to Review tab-> Allow Users to Edit Ranges
    1. Click “New…”
    2. Give the range a title.
    3. “Refers to Cells” should already contain the cells you want to allowing sorting on.
    4. If you want to allow only certain people to sort, give the range a password.
    5. Click “OK”

Step 2: Prevent users from editing these cells

When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

  1. In the “Allow Users to Edit Ranges” dialog:
    1. Click “Protect Sheet…”
    2. Give the worksheet a password
    3. Uncheck the worksheet protection property called “Select Locked Cells”
    4. Check the “Sort” property and the “AutoFilter” properties
    5. Click “OK”

This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.

Solution 2: Using Macros to Allow Locked Cell Sorting

To use a Macro to allowed the sorting of locked cells, you will need to make a macro for every sort operation you would like to allow. For example, ascending sorts and descending sorts would have to be written in separate macros. In addition, not all users have macros enabled because they are a security risk. However, the advantage of macros is that you don’t need to configure your template in any special way.

The macro we will write unprotects the sheet, selects a range called “range1,” sorts range1, and protects the sheet again:

Sub Macro1() 
' 
' Macro1 Macro 
' 
' Keyboard Shortcut: Ctrl+d 
'    
     //unprotect sheet 
     ActiveSheet.Unprotect
     //selects range     
     Application.Goto Reference:="range1"     
     //clears sort
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear     
     //does descending sort, sets sort properties 
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
         .SetRange Range("A1:A4")
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
     //protects the work sheet again
     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
         False
 End Sub

Choosing OfficeWriter Designer or Designer .NET

Overview

OfficeWriter Designer is an add-on for Microsoft Excel and Microsoft Word that allows users to create new RDL reports and design pre-existing reports inside of Excel or Word. Until OfficeWriter 8.6, the original OfficeWriter Designer was the only tool available with this functionality.

However, as of OfficeWriter 8.6, SoftArtisans will include a second, new version of OfficeWriter Designer called the OfficeWriter Designer .NET. The OfficeWriter Designer .NET is a completely rewritten version of its predecessor, created from .NET/C# using VSTO (Visual Studio Tools for Office run time).

Benefits of OfficeWriter Designer .NET

  • Office 64-bit support
  • No dependencies on COM/VBA
  • More robust handling of modifications to queries in Visual Studio/Report Builder
  • Better parameter support with the View functionality, including support for cascading parameters

Which Designer Should I Use?

The new OfficeWriter Designer .NET does not currently support all of the features of the previous Designer. For full functionality support, use the original OfficeWriter Designer. Please see the feedback section below for more information about submitting requests for new features. Continue reading Choosing OfficeWriter Designer or Designer .NET

New Webinar! Make Reports that Measure Up

Take a look at how OfficeWriter can turn your drab Excel reports into chart-topping spreadsheets. This month, it’s all about music as we cover your favorite bands, artists, and labels.

In this webinar we’ll cover:

  • Grouping and nesting in Excel
  • Using SQL Server Reporting Services (SSRS)
  • Charting in Excel and relationships between genre, artists, labels, and album price

When: Friday, August 23, 2013 at 1 P.M. EST

*Register early as space is limited.

Can’t attend? Register anyway, and we’ll send a copy of the slides and recording following the webinar.



Carpe Datum: How to Export Your GMail to Excel

Credit: Hongkiat.com

[Crossposted from Riparian Data]

Straightforward title, straightforward goal, ugly and roundabout (but free!) method of achieving it.

For some time now, I’ve had this goal: download my gmail data, analyze it, and visualize it.

The last time I tried this, I glossed over the whole getting your gmail data into Excel part. This is because I wasn’t able to do all of it myself–Jim had to take my ugly mbox data and make it Excel-readable.

But now, thanks to the basic python skills acquired in my data science class, I can do everything myself! Kinda. The code in part 3 will probably make a real programmer scream, but for the most part, it works–though it’s not fond of commas in subject lines. And if you, like me, are not a programmer–don’t worry! You can still run the code, using my trusty copy/paste/pray methodology.

Alors, here goes:

Step 1: From Gmail to Apple Mail

You have Apple mail, right?  You can also do this with Outlook, and probably other desktop clients.

1) In your Gmail settings, go to the “Forwarding and POP/IMAP tab” and make sure POP is enabled.

2) Now, add your Gmail account to your desktop client o’choice. If it’s already there, add it again–you’re going to be removing this one.

Important: Do not check the “remove copy from server after retrieving a message” box!

Step 2: From Apple Mail to mbox

This part is easy. Just select your mailbox in the desktop client, and go to Mailbox->Export Mailbox, and choose a destination folder.

Step 3: From mbox to csv

If you try to save your pristine mbox file as a csv, you will get a one column csv. Don’t do that. Instead, use these python scripts (also up on github).

The first script opens a blank csv file, and fills it with the subject, from, and date lines for each message in your mbox. I called it mbox_parser.py.

import mailbox import csv
writer = csv.writer(open("clean_mail.csv", "wb")) for message in mailbox.mbox('your_mbox_name'):     writer.writerow([message['subject'], message['from'], message['date']])

If you don’t know what python is, you can still run this script. Here’s how:

1) copy the above code to a plain text file, and save it as mbox_parser.py. Save it to the same folder you saved your mbox file to.

2) open your terminal (spotlight–>terminal)

3) type cd Users/your_account_name/directory_where_you_saved_your_mbox,

4) type  python mbox_parser.py

5) Voila! In your directory, you should see a new file, cleaner.csv.

You’ll notice that the ‘date’ column is a long, jam-packed date string. It’ll be much easier to Continue reading Carpe Datum: How to Export Your GMail to Excel

When to use SUMIF vs. Pivot Tables in Excel

SUMIF and PivotTables can both summarize data based on specific criteria, but they do so in completely different ways. In most cases, PivotTables are going to be faster and easier to get the data that you want, but sometimes using Excel formulas is the only way to handle complicated data.

All the examples from this blog post can be found in this workbook: SUMIF_PivotTable

WHY PIVOT TABLES ARE BETTER

Let’s take a look at a quick example of some fruit sales data, where we want to find information like: all sales for a date, total sales for a fruit in the given time period, or total sales for a type of fruit on a given day.

ExampleData

With SUMIF, you can specify the range of values you are using as the criteria (dates or fruit), the values you want to sum (sales), and the actual criteria that will determine if the values are included in the sum (“7/2/2013”, “Apple”). SUMIFS (new in Excel 2007) extends this functionality to allow multiple criteria (dates and fruit):

ExampleFormulas

Note: Excel also offers COUNTIF, COUNTIFS, AVERAGEIF and AVERAGEIFS starting in Excel 2007.

You can do the same with PivotTables, but the PivotTable will also handle sorting, grouping and organizing your data so you can just lift the aggregated values right out from the table:

ExamplePivotTable

Here the values are automatically generated by the PivotTable. No extra work needed aside from creating the PivotTable, which is as easy as selecting the data range and specifying where the table needs to go.

Excel also applies PivotTable styles, which change be switched in one click and you can even create your own custom styles.

WHY WOULDN’T YOU USE PIVOT TABLES?

I extended my fruit example to use sample data from the AdventureWorks database, where I wanted to compare online and retail sales for North America, broken down by quarter: Continue reading When to use SUMIF vs. Pivot Tables in Excel

ExcelTemplate: How to format alternating row colors

Problem

A common way to display data in Excel is to alternate the background color of every other row when displaying a large table of data. With ExcelWriter there are multiple ways to accomplish this. This post covers some possible ways to apply alternating row colors with ExcelTemplate.

There is another post that discusses how to do this with ExcelApplication.

Solution

Option 1: Format as Table in Excel 2007/2010

Starting in Excel 2007, Excel provides pre-formatted table styles which already contain alternating row or column colors. This is the easiest way to format your data with alternating row colors. Note: these table styles may not render properly in Excel 2003 or in the XLS file format.

To format an area of cells as a table:

1. Highlight the area of cells.

2. Go to Format as Table in the ribbon.

3. Select a table style from the available styles.

4. If you chose to include your table header row, make sure to check off “My table has headers” in the confirmation dialog.

There are basic options for modifying the banding patterns:

You can also create new table styles:

You can do this for ExcelTemplate templates:

When the rows of data are inserted, the color banding will be applied:

Option 2: Use Conditional Formatting

The other approach is to use conditional formatting in the template to achieve alternating row colors. This may be more appropriate if you are not certain if your end-users will have Excel 2007/2010.

1. Create an ExcelWriter template with data markers in Excel.

2. Highlight the cells with data markers that correspond to the data you wish to display with alternating background colors.

3. From the menu, choose Format>Conditional Formatting. The formatting you define for this row will be applied to every new row that will be inserted by ExcelTemplate at runtime.

4. First define the formatting for even rows. In the “Condition1” field, choose “Formula Is” and in the formula field, type the following formula:

 =MOD(ROW(),2) = 0 

This formula uses the MOD( ) function to determine if the number of the current row (returned by the ROW( ) function) can be evenly divided by 2.

5. Click on the “format” button.

6. Click on the “patterns” tab and select a background color.

7. Now set a condition for odd rows, by clicking “ADD” and following the same steps as above but with a different formula:

 =MOD(ROW(),2) = 1 

8. Save the template and use it in your ExcelWriter application.

When ExcelTemplate imports new rows of data, the conditional formatting will also be applied to all the new rows:

Workbook colors are not displayed as expected in older versions of Excel

Problem

When using ExcelWriter’s ExcelApplication object to generate a new workbook, custom colors that are assigned in code to fonts, charts, cell backgrounds, etc., display incorrectly on the client when the workbook is generated.

OR

When creating an Excel File or an Excel file to use as a template with the ExcelTemplate object using Excel 2007 and above, colors are not colors are not preserved in Excel 2003 or older.

Continue reading Workbook colors are not displayed as expected in older versions of Excel

Excel’s color palette explained

Problem

Excel’s color palette contains 56 colors, all of which can be accessed and most of which can be replaced using ExcelWriter (v6 or later). This post describes the layout of the palette and enumerate the default palette colors.

This content is most pertinent to the Excel 2003 color palette, which only has 56 colors. In Excel 2007 and later, workbooks can support millions of colors, but there is still an underlying workbook palette that has 56 colors. For more information about colors in multiple versions of Excel, we have enough post about workbook colors that are not displayed properly in older versions of Excel.

Solution

The palette is split up into a few different sections: Continue reading Excel’s color palette explained

5 Underutilized Excel Features To Take Advantage Of

Let’s get straight to the point, because frankly who wants to waste any more time finagling your data and reports? The following are the top 5 Excel features I use on a constant basis to get the most out of my data.

For reference, you can download the example workbook I used in this post: Top5ExcelFeatures.xlsx

#5 – What-If Analysis

WhatIfAnalysis

I actually only started using this one recently, but it’s quickly become a favorite. I’m particularly fond of the Scenario Manager function. What-If Analysis is comprised of three pieces: Scenarios, Goal Seek, and Data Tables. 

Scenarios has the ability to define a scenario that is associated with a particular set of cell values. You can define new scenarios that are tied to different cell values. When a new scenario is loaded, all of the values update. This is great for flipping between Best Case and Worst Case views of a worksheet.

WhatIfAnalysis2

The other two pieces are Goal Seek and DataTablesIn Goal Seek, Excel automatically computes and finds a calculated value based on the value of another cell, such as finding an interest rate based on a monthly payment. Data Tables allows you to hook up entire tables of values based on up to two variables.

#4 – Sparklines

Sparklines

These mini-charts were released in Excel 2010 and they provide a quick way to Continue reading 5 Underutilized Excel Features To Take Advantage Of

March Madness: Predict Your NCAA Basketball Brackets with Excel

Every year in March, the NCAA hosts the college Men’s Basketball Tournament. Every year, I go through all of the teams, create my brackets, and see if I can do better than my friends. My one downfall is I always have my favorites: you know, the teams you follow because you went to school there, the team your significant other cheers for, your alma mater’s arch-nemesis, or the team with that really cool mascot. Whatever the reason, the biases end up costing you the first round, sidelining you out of the pool. What we need is a simple way to generate our bracket and remove some of that bias.

Instead of using a totally random outcome, let’s use a few of Excel’s analytical features to increase our chances of putting together a winning bracket. Let’s take Mens NCAA Basketball tournament data since 1985 (courtesy of The Washington Post‘s database), that has the history for specific seeds, teams, coaches and conferences – everything from each school’s tournament results to how No. 7 seeds have fared against No. 10 seeds in the first round. Using this data as a starting point allows us to inject reality into our random numbers. Because really, it is not ever worth considering if a 16 can beat a 1, right?

After copying and pasting the data from the webpage into an Excel spreadsheet, it’s time to input the formulas. It’s basically a two-step process. What we need to calculate is the probability of a particular seed winning or losing. If you hadn’t seen it before, a 9 seed beets an 8 seed more often than not, and a 1 seed has never lost. The pivot table I created reflects that, proving to be a useful tool in our selection process. It’s not too difficult to create a pivot table by seed and win/loss and by round for our source data. (See the workbook attached at the end of this post.)

First Pivot Table

Next, we develop a formula that uses the probabilities along with the RAND() function to predict the outcome of a match-up. All we need to do is apply the random number to the pivot table data to determine which of the two seeds advance to the next round. There is no easy way to do this, except with a long and complicated formula. Luckily, most of the formula is calculated by Excel by doing a simple click. The two Excel functions that get this done for us are the RAND() and the GETPIVOTDATA(). RAND() is well documented, but the GETPIVOTDATA() allows us to treat the pivot data like a database to get our probability for a seed to win the match-up.
Bracket

After playing with the output for a few runs, I noticed that the later rounds are fully dominated by the higher seeds. That happens because of the limited data for lower seeds in the later rounds. I want to allow for those Special Case teams to triumph over the Big League teams, so I added Continue reading March Madness: Predict Your NCAA Basketball Brackets with Excel