Protect Cells from Any Unauthorized Changes in Excel

Protecting your Excel Files from any unauthorized changes is crucial.

When you email or share your Excel files with others, there are always chances that they may change some key information, that shouldn’t have been changed.

This can happen because, by default, all cells in Excel are available for modifications by anyone having access to the Excel file.

But the strange thing is that if you were to check Excel’s cell protection setting under Format Cells, you’d find that each cell is marked as Locked.  However, Microsoft is quick to inform you that

Locking cells or hiding formulas has no effect until you protect the sheet.

Quite a strange setting, I believe. However, you can use this setting to your advantage.

Scenario: Preparing a Protected Travel Expense Claim Form in Excel.

Let’s say you are creating a Travel Claim Form for all staff to use in the company when they use their own transport for office meetings or customer visits.

You’d only want the staff to key in their travel expenses, dates, and the type of expenses. You wouldn’t want them to modify the rate which you use to calculate the claim amount. However, you would still want yourself or HR manager to be able to adjust the rate periodically, based on the policy.

To achieve this, you will have to create an Excel Spreadsheet, with careful consideration of the data protection. Your Excel file must have:

  1. Some cells are protected completely (no changes allowed by anyone) These could be formulas and any master data like names of departments, cities etc. More like master data that does not change often.
  2. Some cells that can only be modified by you or HR. These could be the Rate of reimbursement etc. You could even set a password to allow only authorized people with the right password to key in this information.
  3. Some cells that anyone can modify. These would be the cells where the staff is going to key in their expenses. Mostly, such cells would be the ones accepting the data entry values only.

Here are the detailed steps that you can take to protect the information and make a spreadsheet that everyone can use, without fear of getting inadvertent changes to the file, and maintaining full privacy and security.

Step 1: Unlock all Cells in your Excel Template.

To do this, Select All Cells (you can press Control A or press the small rectangle above row 1 on the top-left area of the spreadsheet. Once all cells are selected, right-click to activate the menu, and go to Format Cells. Shortcut is Control 1 for this option.

Now move to the Protection tab, and uncheck the Locked checkbox.

Click OK and come out of this popup. Now all cells are unlocked.

Step 2: Lock the Calculations & Totals Row/Columns

Select the cells that have any calculations. To select multiple cells, highlight them, and hold the Control key to multi-select several formula and total ranges.

Now go to Format Cells by right-clicking, or press Control 1 again. This time, you can check both checkboxes on the Protection Tab. Lock and Hide the formulas and cells completely.

Step 3: Select Cells That can only be modified by authorized Users

Finally, it is time to select the cells that need to be protected from unauthorized changes. So select those cells, again by using the Control Key to multi-select the cells.

Once the cells are selected, go to Format Cells > Protection Tab, and Lock them. Click OK.

Step 4: Set a Password to cells that only an Authorized User can modify.

Go to Review Tab on the Excel menubar. Select Allow Users to Edit Ranges, from the Changes group on this menu.

Click New, and key in a name for the cell range. Set a password that will be required to be keyed in whenever anyone tries to change such cells. You have to key it in 2 times to confirm the password.

Step 5: Protect the Sheet

The previous 4 steps will have no effect until you protect the entire sheet.

To do so, go to Review > Protect Sheet.

You can then, optionally, set a password. Although I say optionally, I’d recommend that you definitely do it as a best practice. otherwise, any user can modify your template, simply by unprotecting the worksheet, make any change, and protect it again.

You will be required to key in the password twice, to confirm that it is the correct password.

Step 6: Test the Protected Sheet, and deploy it.

Now you are ready to test your protected Excel Template.

Users should be able to key in their particulars, expense, mileage etc. But they would not be able to see the formulas behind the calculations.

Neither will they be able to edit or change these totals.

And best of all, they can never edit the reimbursement rate. Even if they click it inadvertently, Excel will ask them for a password.

As for HR, they should have the password set by you. With this password, they should be able to edit the reimbursement rate, and then save the template for use in the future, with the newly revised rates.

Voila, quite a sophisticated Excel template, with worksheet protection, hidden formulas and authorised user-editable cells created in no time.

Give it a try. I’m sure you’d love this simple method to protect your data files from unauthorised changes.

Written By Vinai Prakash,
Founder of ExcelChamp.Net & Intellisoft Systems
Vinai has over 30 years of experience working with spreadsheets and teaches Excel, Advanced Excel, Excel Data Analysis, Excel Dashboarding Techniques at Intellisoft Systems in Singapore, and online at ExcelChamp.Net.

 

 

 

 

6 New Chart Types in Excel 2016

Before the year 2016 begins, Microsoft has already unveiled Microsoft Office 2016 suite – with a number of enhancements, features, and completely new things that extend the existing Excel and takes it to new levels.

In the latest and greatest Microsoft Excel 2016, we see 6 new types of charts, which will help to transform the data into much better insights, information and visualization delight than ever before.

Microsoft Excel 2016 boasts New Charts that can display:

  1. Sunburst Chart
  2. Treemap Chart
  3. Waterfall Chart
  4. Stock Chart
  5. Pareto Chart
  6. Box and Whisker Chart

shows the new charts in PowerPoint2016

The Sunburst chart looks like a pie chart, but has rich, extended functionality. You can now visualize the data at multiple levels, which was simply not possible with a pie chart.

Business Analytics in Excel 6

The Waterfall chart in Excel is a welcome addition. Previously, we had to write cumbersome VBA code, and even use external charting applications to create waterfall charts. This type of waterfall chart is great to show stock price movements.

Business Analytics in Excel 7

A Pareto chart shows the 80-20 Rule, which applies to any business, in any industry, and has been proven to be a great indicator of the top KPIs that make the difference. Doing a 80-20 Pareto Analysis required us to Build a 2 Axis chart in previous version of Excel (like Excel 2013, Excel 2010 or Excel 2007 etc.)

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Excel 2016 Software: If you do not have the Microsoft Excel 2016 software yet, you can easily get it online here.

Want to Learn Excel 2016: There are several books on Excel 2016 already available, and you can also join the ExcelChamp’s Online Training for the Pivot Table MasterClass, available on TruEducate website. A few, short videos will teach you the master techniques that are used to play with Pivot Tables, and generate powerful reports from Excel Data using Pivot tables. This video training is recorded and provided directly by me, Vinai Prakash, at the TruEducate Website.

These new chart types in Excel 2016  will help us in creating beautiful charts in Excel, and take it to the next level of visualization of data, and presentation for our clients, management, users, and for our own data analysis and charting analysis.

In the coming weeks, I will be highlighting more new features of Microsoft Excel 2016. Do let me know if I can help you in any way in using Microsoft Excel 2016.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Cheers,
Vinai Prakash
Founder: ExcelChamp.Net – Simple Tips to Get More out of every day Excel, and be an ExcelChamp!

7 Habits of Highly Effective Data Analysts

Converting Data Into Information Using Excel
Converting Data Into Information

We all are sitting on mountains of data, and new data arrives each day in the form of Reports, CSV files, Charts from Marketing, Logistics, Sales, Websites, Google Analytics… Before you can make any sense of it, even more data will arrive.

Today we have much greater processing power in each computer than 10 years ago, yet we are not making appropriate use of it to process the data and create information.

I am sharing some of the best techniques used by data warriors & power business analysts. These are not really secrets… but best practices, that aid in converting data into actionable information.

1.    Clarity of Objectives: Before you begin your gold mining, define some broad goals or identify some of the problems faced by you or your company.

Is it low sales, low margin, low traffic or high CPC?

Once you have clarity on what exactly you are trying to analyze, you can begin our data analysis.

2.    Clean the Data: Most raw data arrives in a pretty bad shape. You need to remove duplicates, fill in some missing blanks or values, and get dates in a uniform format. This will make the later steps easier… or else it will be garbage in & garbage out. To check if the data looks good, try to sort it on different criteria, and have a look around. If it looks clean and complete, then you can begin the next steps in data analysis.

3.    Spot the Trends: It is easier to identify some trends in the data, and then analyze them further. There are several ways to spot the trends quickly. Some common methods are to visualize your data with the 80-20 rule. Identify which 20% of the factors contribute 80% of the results. Create bar charts, sort in descending order, and create a cumulative frequency chart with both axis to generate a quick Pareto chart displaying the 80-20 rule.

Another excellent way is to generate measures of central tendency – using Mean, Median, Mode, Outliers, Range, Variability and Skewness of data. They tell quite a lot about your data pretty easily, and make it easier to spot trends within the data.

Converting Data Into Information With Excel
Converting Data Into Information With Excel

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


4.    Set up KPIs: Create a set of common Key Performance Indicators (KPI) for your line of business/company, so that everyone using the KPI will have a common understanding. Right KPIs shed light of performance and makes it easier to understand areas of improvement.

Some of the common KPIs you could set are ROI, EBITDA, Net Profit Margin, Customer Lifetime Value, Market Share, Brand Equity, Cost per Lead, Customer Turnover Rate, Earned Value, Quality Index, Carbon footprint, or Supply Chain Miles.

With KPIs, and their trend, you can then find the story told by the data. Identify the reasons and take appropriate actions. Tracking KPIs over a long time period makes it easier to spot trends in seasonality, sales patterns, demand surge and profitability across months and quarters.

5.    Common Repository for Data: Set Up a common data repository, from which everyone draws data. It is quite common in larger companies to have multiple islands of data. Everyone seem to have a ghost server under their desk, compiling data from different sources and reporting off it. Thus, different stories are told in the board room, and the management often wonders which version is really the truth?

A common source brings more sanity, and trust on the data and reporting. A common data warehouse from where all management reports are generated is a great idea.

6.    Visualize Using Charts, Graphs & Dashboards: A picture is worth a thousand words. Rather than creating voluminous reports full of numbers, display the summarized information in the form of line charts, bar charts, spark lines and various other chart types. What may not be visible in data may jump out at you visually, in a chart. It is much easier to find actionable insights in charts. Fortunately, most data analysis tools come with excellent charting capabilities.

Create Simplified Reports Using Dashboards. Multiple summarized reports and charts can be compiled into a management dashboard. With key KPIs, charts and data visible on a single piece of paper or screen, it becomes much easier for senior management to make quick decisions.

Dashboards are dynamic, making it easier to compare month on month, quarter or quarter, division to division performance and spot trends quickly.

These visual implementation must be idiot proof – so simple that a O level student should be able to interpret it pretty easily.

Use simple tools for the analysis. It is not necessary that the next shiny reporting tool or expensive BI tools will make it a breeze. It takes many months of painstaking work to get to a standardized dashboard. A visually appealing and simplified dashboard makes analysis and reporting fun, something to look forward to.

7.    Constant And Never Ending Improvement (CANI): Experienced analysts are always on the lookout of opportunities to further extend their analysis, improve their dashboards and identify new insights. Ask your clients and users how they use the reports and dashboards, and seek ways to improve it.  Be open minded, flexible, inquisitive and persistent in your pursuit of information excellence. Ogle at your data from different angles and different perspectives. It will enable you to discover new insights and add value to your business.

Implementing these best practices will enhance your data analysis experience, and will enable you to create value for your clients, bosses, and with the new insights found, you can improve your business performance, productivity, and profits!

Cheers,
Vinai Prakash

About The Author: This article has been written by Excel expert Vinai Prakash. Vinai has over 28 years of experience in business intelligence, data mining, and creating useful management dashboards and reports.

Vinai runs his own training company Intellisoft Training, and has coached over 5,000 executives and management on creating dynamic dashboards using Microsoft Excel. Vinai runs his blog on Excel Tips & Techniques at http://www.ExcelChamp.Net

Vinai Prakash will be conducting a 2 day Excel Dashboard MasterClass in Singapore in December this year. Contact http://www.intellisoft.com.sg or call +65-6296-2995 for more information.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!