Track Changes in Excel

In today’s environment you will come across with many instances where you have to share your workbook to get used by other multiple users. Now question comes what if they have done any change how will you know as an administrator. You might want to track when the change was made, who made the change, what cell the change occurred in and what data was changed.

This article will help you to do get a solution for above problem.

Excel is having a built in feature which can help us to track the changes in excel file. We can easily review the changes on the worksheet and we can even accept and reject the changes.

There are couple of rules which we need to follow while using above feature

        1)    We cannot revert the worksheet to its original state. If you want to do the same check the tracking record    and change the value manually.

        2)    Tracking changes just record the data changes not the formatting changes

        3)    By default history will be available for 30 days.

        4)    As soon we will start the tracking, workbook will become shared workbook.

Here the steps which we can follow

Create One Excel File

Click Review Tab

Click Track Changes

Click Highlight Changes

Click on Track Changes While editing

Select information for When,Where and Who

Provide Cell No in reference for which you want to tracking to enabled

That’s it, now whenever anybody will change the values, cell will highlight and if you move cursor to that

We have the option to track the changes also. We can generate the summary report by following below steps

Click on Review

Click in Share Workbook

Go to advanced tab, change the days and click ok

Select the highlight cell

Click on Track Changes

Select highlight changes

Select checkbox of “List Changes into new sheet”

That’s it your changes history will be available in new sheet like below

At some point you’ll need to either approve or reject the changes. You can review all the changes and then pick and choose which ones you want to keep or discard.

Simply click on Track Changes again and choose Accept/Reject Changes. Select the options to choose which changes you want to accept or reject. If you want to review all changes, just leave When checked and make sure it is set to Not Reviewed.

Click OK and Excel will begin to show you each change that was made and give you the option to Accept orReject. You can also Accept All or Reject All changes if you like.

Click on Review

Select Track Changes

Select Highlight Changes

Accept/Reject based on situation

Hope this feature will help you in many ways..

Thanks,

Sanjay Bakshi

mesanjaybakshi@gmail.com

Cloud Computing for Beginners

Cloud Computing is a model for enabling network access as a shared pool. This concept provides clients to store their data to third party data centre. It relies on sharing of resource over a network. It maximizes the effective use of shared resources. Resources available in cloud are not only used by multiple users but allocation happen based on demand. This approach helps clients and companies to reduce their cost.

When you read that a particular company is moving to cloud that means that they are not going to purchase any hardware/software or any technical CAPEX item and will the take those services via cloud.

There are many benefits we get as a company once we move to cloud.

• Data Centralization
• Cost Reductions
• Resource sharing
• Continuous Monitoring
• Easy Maintenance
• Reliability

Large companies which wanted to move to cloud they need to finalize that which kind of services theywanted to use.

•   IAAS (Infrastructure as a Service)
•   PAAS (Platform as a Service)
•   SAAS (Software as a service)

        IAAS

        If we go with Infrastructure as a service we get virtual machines, servers, storage and network

        PAAS

       If we go with platform as a service, we get a computing platform which includes OS, Environment for programming, database and servers. Software developer can built the code and execute

        SAAS

       If we go with software as a service then we can get the access of application software and databases. We can go with software and even can pay based on usage.

       Once we finalize what kind of services we are going to use we decide on deployment. Basically there are many deployment methods are available but three kind of deployment are very common  

•            Private Cloud
•            Public Cloud
•            Hybrid Cloud

         Private Cloud

         This deployment will be for a single organization, owner can decide whether that will be managed by company    people or nay third party. Once you feel like going on private cloud you need to decide what you are going to      do with your current infrastructure. This requires significant level of changes and this will be costly also.

         Public Cloud

         Public cloud services may be free and you can opt all the services which are open for public use. Services        which we used via public cloud go via non trusted network.

       Hybrid Cloud

       Hybrid cloud is a composition of two or more clouds (Private, Public or any other). System treats these different entities but will be bound together. This deployment helps system manager to identify services based on actual requirement. Hybrid cloud is one where IT organizations use public cloud computing resources to meet temporary capacity needs that cannot be met by the private cloud.

      

      Having said that there are some privacy and security issues which comes in the picture like

      Cloud computing poses privacy concerns because the service provider can access the data that is on the cloud at any time. It could accidentally or deliberately alter or even delete information. Many cloud providers can share information with third parties if necessary for purposes of law and order even without a warrant. Users can encrypt data that is processed or stored within the cloud to prevent unauthorized access.

      Keep Reading

      mesanjaybakshi@gmail.com

Use of Name Manager in Excel

Use of Name Manager in Excel

Name Manager helps user to define names for their formula which actually help them to understand formula easier.

Name Manager comes in use when you are creating any excel file with multiple references, like working on “Employee Appraisal Data” or “Financial Budget” or “Financial MIS” etc.

Let’s see how this works, we will take an example of electronic shop

I am providing Cell No inside the grid to understand how it works

Normal Way

Product Code	Product Name	Unit Price	Quantity	Total Bill
P1	TV	76000(D2)	2(E2)	152000(E2*D2)
P2	Washing Machine	24000(D3)	3(E3)	72000(E2*D3)
P3	Iron	1000(D4)	10(E4)	10000(E2*D4)
P4	Mixer	2000(D5)	12(E5)	24000(E2*D5)
Total with Formula				258000
Discount%				20%
Actual Bill				206400

Total with Formula= SUM(F2:F5)

Actual Bill = F6-(F6*F9)

With Name Manager

1) Select Formula TAB

2) Select Name Manager

3) Click New

4) Provide name (In above case, I have given “Final_Total”)

5) Provide Cell Reference (cell reference is sum of total bill)

Now follow same steps for Discount

1) Select Formula TAB

2) Select Name Manager

3) Click New

4) Provide name (In above case, I have given “Discount”)

5) Provide Cell Reference (Value of Discount% will be the cell reference for this)

Now in Actual Bill we can write formula directly Final_Total-(Final_Total*Discount) rather than giving formula.

This will make easy for the person who is reviewing excel sheet first time, no need to run around with cells and understand how you have arrived the value of Actual Bill

These are the basics of implementing and managing names that any Excel user should know. Once you get used to names, you can create and use formulas with ease. It may take some time to learn and get into this habit, but I suggest you put in the effort. It’s well worth it!

Keep Reading

Sanjay Bakshi

mesanjaybakshi@gmail.com

SQL 2016 : Always Encrypted

Recently I have gone for Annual Summit of SSGAS2015 and got a chance to grasp some knowledge from some top SQL Server Expert and the main topic was SQL Server 2016.

There are a lot of new things coming up in this new version and one of the features which I like was Always Encrypted.

In the days of SQL Server 2005, column level encryption was allowed within database engine. We used to encrypt the sensitive data by an application and decrypted whenever is required. Post encryption data will be encrypted on disk, in memory and on the network. However column level application used to require changing the application code with the help of functions. e.g Encrypt By Key and Decrypt by Key.

When SQL Server 2008 came into existence it got option of TDE (Transparent Data Encryption) which will encrypt the database without having to change the application code or using any third party tool. We actually got two concepts in TDE. First are “In Rest” and “In Motion”. Data in rest will be encrypt but as soon as data will be in “In Motion” state like into memory or over the network, the information will decrypted automatically and therefore not protected.

Now with SQL Server 2016 Microsoft has introduced “Always Encrypted” option which includes end to end encryption without the need for application code change. Always Encrypted provides a separation between users who own the data and users who manage the data, such as administrators, because the encryption/decryption occurs at the client layer.

So your code will look like this

Create Table Contact

(

Contract_no nvarchar(60),

Contract_Short_Desc nvarchar(1000)

            Encrypt WITH (COLUMN_ENCRYPTION_KEY=CEK1,

            Encryption_Type=RANDOMIZED,

            ALGORITHAM=’Algoritham Value’

)

GO

There are two kind of Encryption Type available

Deterministic encryption - a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region.

Randomized encryption - a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.

Two types of encryption keys are required for encryption: A Column Encryption Key (CEK) and a Column Master Key (CMK). The CEK is used to perform fast, synchronous data encryption while the CMK is used to encrypt the CEK asynchronously for high security.

We can get Algorithm value under Column Encryption Key Properties. Once we set the algorithm and enable the Column Encryption Setting enabled we will get the query result like below. 

There are some constraints as well available with Always Encrypted

   In Deterministic Encryption queries can perform equality comparison but we will not be able to use greater/less, pattern matching or any arithmetical operations.

   Queries cannot perform on columns encrypted by using randomized encryption

Currently, the only driver supporting Always Encrypted is SqlClient in .NET Framework 4.6.

     Following data types are not supported

o    Xml

o    Image

o    ntext

o    text

o    user defined data types

o    sql_variant

o    hierarchyid

Happy Reading

mesanjaybakshi@gmail.com