How to retrieve an Excel file crashed due to System Shut down

 If you are in a situation where your computer suddenly switched off and the Excel file you were working on got closed without saving.

Now for the below method to work your need to have "Auto Save" feature turned on in Microsoft Excel 

To check if Auto Save is turned on follow the below path in your Excel

File-->Options-->Save, now on the right hand side tick the "Save AutoRecover information every X minutes and also tick the "Keep the last autosaved version if I close without saving" option.

Now whenever you excel crashes, the most recent version which was autosaved despite you not saving it will be available when you open a blank excel workbook.

If you want see other versions of the crashed workbooks which are not saved. follow below path.

File-->Open-->Scroll till bottom and click on "Recover Unsaved Workbooks"

 

What are the limits if the row header and column header in Excel sheet?

 With Microsoft Excel 2016 the total rows an Excel worksheet can have is 1048576 rows. In terms of columns, you can have 16384 columns.

Well, that's a huge number of rows and columns to play with but, remember, if you fill each cell in these rows and columns the performance of the workbook will not be optimal. Yes Microsoft has provided avenue for entering data but to processing should also be supported by a good chip and board.

Probably, you can go for the cloud version of Microsoft Office (365) which should perform with a good internet connection.

In which tab hyperlink option is available in Microsoft Excel?

 Hyperlink in excel.

Text in a cell can be hyperlinked to either a webpage or to a place in the same document or email. Hyperlink option is found under the "Insert" tab in the ribbon at the top.

Once you select the desired cell and click on the hyperlink it would present with the following window.

Here you can customize the link based on your needs.

How is Data "Analysis" different from plain reporting : AKA Synopsis writing

When we send out reports. We are more often than not in “URGENT” mode. Unless or until mentioned we never send any insights to the person who requested the report/analysis. It may be a very “SMALL” step to write your “UNDERTANDINGS” as synopsis, but doing it consistently has three benefits.

1. You will know what is the report saying and hence understand the business
2. You are virtually doing a self QC.
3. You are creating a “BRAND” for yourself with some important insights/actions that you share.
4. When writing synopsis, you be bold. You are NOT writing it to please the other person. Clearly mention what you speak in mind.

Now the big Question you guys have is. We are looking at this data from long time. What new can I see now. That’s when you come out your report and try to co-relate other aspects of business. 

Remember writing synopsis will improve your Professional value, IMPROVE your personal accuracy and self-satisfaction. Trust me when I say this. It’s a very intensive activity but it is one of the best teachers you can get.

Is there a free database solution?

 Yes. Oracle Database XE is free for fall and supports a database of 12GB. Its is easy to install and hassle free experience.

Oracle Database XE can be download from: https://www.oracle.com/in/database/technologies/appdev/xe.html

During setup you are required to enter few admin passwords. Ensure you remember them. These are required to interact with the database at a later point of time. 

Now once you have installed the Oracle XE database how to query data or load data?

For this you need a front end application. In this case you can use Oracle SQL developer which can be download from this link depending the you machine's architecture: https://www.oracle.com/tools/downloads/sqldev-downloads.html

Once you have installed SQL developer. Add a connection using the top left green plus button. Below screen will appear. Name the connection as you require. in the ROLE adjacent to username box choose SYSDBA.

Enter Username as "SYS" and the password you have configured during setup of XE. Hostname, port and SID should be entered as per below and you should be connected to your XE database

ROLLUP function: How to get sub total row in a Oracle SQL query as output

 In oracle SQL query when you query for any data, the output is table of columns and rows selected. But there is not total added at the bottom.

How to add a total at the bottom and also how to add a sub-total for each change of value in a specific column.

select fruits, city, sum(price) from fruits_table group by rollup (fruits,city)

In the above example the table a total will be added to FRUITS level and also a sub-total will be added at each change in CITY.

However, there is catch. If there is more than one dimension (in the above example FRUITS & CITY) then you should sort the table by CITY and Fruits so that the sub-totals appear in order. Below the updated version of the example query with ordering.

select fruits, city, sum(price) from fruits_table group by rollup (fruits,city) order by city,fruits

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com

How to unhide all the hidden sheets at once - Microsoft Excel

 When working with big dashboards we hide lots of backend sheets so that final dashboard is clean.

The easy part if hiding multiple sheets, this can be done easily. But how to you unhide all sheets or specific sheets.

Below code will help you unhide all the sheets. hit Alt+F11. Go to Insert Menu -> "Module" and enter the below code.

Sub UnhideAllSheets()

'Unhide all sheets in workbook.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

But what if only few sheets needs to be unhidden. In these cases use the below code which will prompt you before unhiding a hide until the last hidden sheet.

Sub UnhideFewSheets()

'Unhide all sheets in workbook.
Dim ws As Worksheet

Dim question As VbMegBoxResult
For Each ws In ActiveWorkbook.Worksheets

If ws.Visible = xlSheetHidden then

Question = Msgbox("Do you want to Unhide" & ws.Name &"?", vbYesNo, "Sheets will be unhidden")

If Question = vbYes then ws.Visible = xlSheetVisible

End If

Next
End Sub

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com

How to get contents from a Hidden sheet in a Protected workbook - Microsoft Excel

 When you have a Excel file which is protected and you have forgotten the password.

Now how do you retrieve the content from the hidden sheet?

There is a simple technique but the requirement is you should know the sheet name of hidden sheet or you VBA environment should not be password protected.

Lets assume the hidden sheet name is 'Sheet2'. To retrieve only the content from this sheet, all you have to do is write a A1 cell reference formula in a visible sheet like below. Now drag this to right and down as much as needed. Voila! you have the data from hidden sheet in front of you.

If you do not know the sheet name. Then hit Alt+F11, on the top left side you will see all the sheets of the current workbook. Based on your needs you can get the data from a hidden worksheet.

Again, this tip is not intended to be used in illegal ways but only in cases it is desperate need and there is no other option.

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com

How to filter for multiple values in Microsoft Excel dashboard

 When it comes to dashboards in Excel, we rely on the data validation list predominantly to act as user inputs based on which the dashboard is calculated. But this approach doesn't enable the user to multi-select values from drop down nor there is other straight-forward way which makes the life of end user to create such functionality.

So given this case, how do we achieve this?

Well, we can use the form checkbox and a bit of VLOOKUP to get around this and have multi-selection in your dashboard.

Below are the steps which should help you achieve this. Before that lets define our end goal. 

Lets say we have to enable to the dashboard to show total sales of fruits by month and the user needs to have a functionality of selecting single/multiple months.

Step1: Below is the data we are working with.

Step2: Lets have a simple table like below which should calculate sales based on dynamic month selection.

Step3: Enable the DEVELOPER tab on the ribbon above. If already enable skip step 3.

Click on 'More Commands'  in the 'Customize Quick Access toolbar' menu. Now 'Customize Ribbon'. Select the 'DEVELOPER' on the right-most slide and click OK.  

  

Step4: From the developer tab insert a checkboxes as shown below and name it with the months you have in your data.

Step5: Right click on each of these checkboxes(go to FORMAT CONTROL) and link them to a cell. So that when you CHECK the checkbox the cell should show "TRUE".

Step6: Like shown below. Enter an IF condition to display the relevant month if relevant checkbox is selected adjacent to the linked cell.

=IF(F5,"JAN","")

When you select a month only those months should appear next to linked cells.

Step7: In the data which I mentioned in step1, add a column "MONTH DYNAMIC" and enter the below formula. So that if searches if the month in the line is available next to linked cells. Now apply the formula to all lines in the data. Now you see only months selected are populating in the data.

=IFERROR(VLOOKUP(K4,$G$4:$G$10,1,0),"")

  

Final step: Enter the below formula in the table shared on step 2. Apply to all rows.

=SUMIFS($M:$M,$N:$N,">""",$L:$L,C8)

Now the table populates data based on which months you select from the checkboxes.

A copy of the file used in the above steps can be downloaded HERE

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com