Excel Error – Pivot Table Does Not Group Dates Properly or Give Errors

I work with large data in spreadsheet and pivot table comes very handy in summarizing  the data. However, often I encounter with an error  when I try to group the multiple sets of date.

Types of Pivot Table Date Grouping errors

  • Pivot Table does not Group dates properly – It gets grouped into just one Group namely ‘Group 1.’
  • Sometimes it gives an error that that data set can’t be grouped.

Various reason for not grouping dates

  • Dates are in text format or non-date format
  • There are extra characters hidden in some of the cells of date column
  • Date locale issue – your locale (PC) uses DMY date format and the text date is using MDY date format (or the other way around)
  • Selection Issue

How to fix this error?

a) Convert Text Formatted Dates into Real Date Format

  • Select the date column
  • Hit Ctlr + H to open ‘Find and replace’
  • Type / (forward slash) in ‘Find what’ and in ‘Replace with’
  • Hit Replace All
  • All Text formatted date will be converted into Real date format

Type / (forward slash) in ‘Find what’ and in ‘Replace with’

b) Remove extra characters in some of the cells of date column

  • Select the date column
  • Hit Ctlr + H to open ‘Find and replace’
  • In Find what space, Type Alt key, and then type 0160. Nothing will type in space and it will remain empty. Also, Keep Replace with space blank.
  • Hit Replace All
  • All Text formatted date will be converted into Real date format
  • Also, make sure to manually remove any visible funny characters, words, alphabets.

1) Type Alt key 2) Type 0160, Nothing will type in space 

C) Fix Date Locale Issue

  • Select the date column
  • Go to menu >  Data > Text to Columns, select Delimited options and hit next
  • Keep everything blank in this window and hit next
  • Select date and in drop down menu select the date type that your system is using. You can use trial and error method by selecting different format and see what works for you.
  • Hit finish
Go to Data> Text to Columns, select Delimited and hit next.
Keep everything blank in this window and hit next
Select date and in drop down select the date type that your system use. You can do trial error by selecting different format and see what works for you.

D) Fix Selection Issue

After creating Pivot table if you do not select date properly then also it may not group as per month and year.

Sometimes, if you select dates not properly then it will get grouped into just ‘Group1’

To fix this selection issue. All you need to do is select any cell and take your cursor to lower part of selected cell until a downward arrow appears and then click to select all other cells.

1) Select any cell 2) Take your cursor at lower part of the cell until downward arrow forms 3) Click to select rest of the cells
All cells will selected like this then right click and hit Group

Please let me know if all these tricks solves your Pivot table date grouping issue or not.

How to Fix – Macbook’s Battery Not Charging? Fan Problems? Overheating?

If your Macbook Pro or MacBook Air is giving you hard time with issues like :

  • The battery is not charging.
  • The cooling fan has suddenly started making non-stop loud noise and it won’t go no matter how many times you restart the system.
  • MacBook get frequently hanged and you lost all your work.

There is a simple fix to all these problems. You just need to reset your PRAM and SMC.

Before you go further, it is highly recommended to take back-ups of your important files and photos.

How to reset the PRAM?

  1. Shut down your MacBook by pressing power button for longer period. 
  2. Once your system is completely shut down. Press the Power button.
  3. Then, Press Command Option P R at the same time and keep holding these keys until the grey screen appears.
  4. After sometime, you will hear the Macbook’s Start-up sound. You can now release the keys, and let your MacBook do the rest of the work.
  5. You are done.
1) Press the Power button.
2) Then, Press Command Option P R at the same time.

How to reset the SMC of your MacBook Pro or MacBook Air?

  1. Shut down your MacBook by pressing power button for longer period.
  2. Keep the power cord of your MacBook plugged in.
  3. Press Shift-Option-Control Power Button at the same time and then let go
  4. Now, turn your MacBook on.
Press Shift-Option-Control Power Button at the same time and then let go

How to Fix – “Be careful parts of your document may include personal information” Error Message in Microsoft Excel

Lately, my Excel file started popping-up an error messages. It’s so annoying as it pops-up every time I save the file which I do like every 30 seconds.

“Be careful parts of your document may include personal information that cannot be removed by the document inspector.”

I fixed this error as below –

Step 1 – Open your spreadsheet and go-to File

 

Step 2 – Click Options

 

Step 3 – Go-to Trust Center

 

Step 4 – Click Trust Center Settings

Step 5 – Click Privacy Options and Un-check the box that says “Remove personal information from file properties on save” and click OK.

 

Done. That annoying message will now stop bothering you.

How to convert an URL/html to PDF?

Many times we need to convert an URL to PDF, reasons might be different, the solution is pretty easy. You do not need any tool (if you Google, you will get many), you don’t need any software. This can be done right from your browser, see the process below.

  1. Open the URL you want to convert
  2. Now press Control+P (the one which is used for printing)
  3. Now click on the change button
  4. Now you would get a box like this, click on save as PDF
  5. And TA DA…. you are done

Not to mention, the same process can also be followed for any html file you have. All you have to do is to open that html file in your browser and follow the same process.

Thank you accepted with a welcome note 🙂

How to Install and Play Pokemon Go in India

Are you looking for a way to download Pokemon Go on your Android or iOS phone in India?

Since this game is only available in few countries and India not being one of them, you might get disappointed that you can’t play this game. Well, don’t get disappointing, here ‘s how you can download, install and play this game in India, both in iOS and Android.

How to download, install and Play Pokemon Go in Android

  1. Visit this link
  2. Download the APK file
  3. Go to your settings>Security and check “installation of apps from unknown sources”
  4. Once downloaded. click on it to visit the APK file
  5. Click on Install and you are set to play Pokemon Go on your android phone in India

How to download, install and Play Pokemon Go in iOS

For iOS the installation is slightly different but good god its still possible and nowhere near as hard as the roblox hack. So you can do it in two ways, the first is to borrow an Apple id and the second one is creating a new apple id alogether

Borrow Apple ID

  1. Ask a friend to share his Apple id and password who lives in a country where Pokemon Go is available
  2. Log out from your Apple id and enter his ID and password
  3. Now search for the Pokemon Go game on App store and download and install it
  4. Since its not your ID,  you ill have to sign out and sign in again with your own ID
  5. Reinstall again Pokemon Go from your saved files from cloud

Create a new Apple ID

Here’s the process is almost same, the only difference is instead of borrowing an ID, you create one by yourself

  1. Sign out from your Apple ID
  2. Go to setting and select General>Language and Region>Change region to US or Australia and or Canada
  3. Open App store
  4. Create a new Apple ID
  5. Add billing as None and select an Address of the country which you mentioned earlier
  6. Now search and download Pokemon Go
  7. Install Pokemon Go and sign out from this fake Apple ID and sign in to your original Apple ID
  8. Your installation will be lost but since your data is saved on cloud, you can install the game again.

Hope this helps, do play the game and let us know how you enjoy this.

How To Download Facebook Videos

These days we tend to watch more videos on Facebook over Youtube. Mostly, they are shared by our friends or are related to our interest area. Many times, we come across videos that we would like to keep it for future use. One way of doing that is saving the link or sharing it in our own timeline. However, what if, we would like to share outside the Facebook universe? For that, we do have various phone apps and websites which helps to download these videos. One such website which works well for downloading Facebook video is FBDown.net.

Steps to Download Facebook Videos

Step 1# You need the URL or link of the video. How to get that?

Right click on the date or time of the video and copy the link.

how-to-download-facebook-videos-link

Step 2# Go to fbdown.net and Download

Enter the copied video URL or link in the input space provided in site and hit download

Select the video quality by clicking it and you are done.

how-to-download-facebook-videos

How To Unblock Yourself on WhatsApp If Someone Has Blocked You

In last post I mentioned about how to find if someone has blocked you on WhatsApp. Today, lets find out how to unblock yourself. It’s a simple trick that can come handy if you want to reach out to someone to clarify any sort of misunderstandings.

Following steps will unblock you, if someone has blocked you –

  • Go to WhatsApp settings
  • Click account and then click on delete my account
  • Go ahead deleting your account by entering your phone number and delete your account.
  • Afterwards, uninstall WhatsApp from your mobile.
  • Then, restart your phone.
  • Now, again install WhatsApp with same old number.
  • Once installed, you’ll get unblocked from all other accounts.
  • However, you’ll be removed from all your existing groups. So choose this method sparingly.

How To Find If Someone Has Blocked You On WhatsApp?

Have you ever tried to find out if someone has blocked you in WhatsApp? I hope you don’t have to be in that situation. However, there are various indicators that may tell you that, you are blocked by someone:

  1. Firstly, you will not to see their ‘last seen’ or ‘online’ status.
  2. Also, you will no longer see any updates to their profile photo. You will always find same old photo.
  3. Moreover, if you try to send any message to them, you’ll always see one check mark which is a message sent indicator. You will never see the second check mark which is a message delivered confirmation.
  4. Lastly, if you try to call them then it will not go through.

There is no direct way to know if someone has blocked you. But you see all these indicators then most likely that person has blocked you. And telling you that, he or she is really annoyed with you. However, often some people turn off all these four indicators for privacy issues. Hence, you may never know if someone has really blocked you or just avoiding answering your messages. But whatever is the reason, just try to get connected with the person through other ways to apologies, just in case, if any of your action has offended them.

Interactive Excel Graph – How to create?

This article will be very useful for people who work on excel and create a lot of reports and wants to create interactive excel graph. Often your clients or boss asks for details on a graph and then you have to go back and make changes and appear in front of them just to be asked for another change.

Story Behind Interactive Excel Graph

Very recently a client of mine was asking me to prepare few reports and every time I prepared something, she was asking for some other and it was taking a lot of time. So basically I was trying to find a solution to this and I came across this interactive excel graph which can present data on multiple levels and as you change your parameters or range, the graph will change simultaneously. The fun part is you don’t have to go back to your data and make changes there, you can make those changes right in the graph.

Process

  1. Select your data set, Interactive Excel Graph
  2. Go to Insert tabInteractive Excel Graph
  3. Click on Pivot tableInteractive Excel Graph
  4. You will get a pivot table like this. Now select parameters that you want to show in your graph Interactive Excel Graph
  5. You should get a pivot table like this. Now select this pivot tableInteractive Excel Graph
  6. Go to insert tab and choose the graph you prefer. I have chosen column graph here Interactive Excel Graph
  7. You should get a graph like this Interactive Excel Graph
  8. But wait, the catch is here. At the left bottom side of the graph, do you see few tabs? Yes, these are your parameters of your graph which you can adjust right there. For example I have changed the parameter Category here and I have only taken Office supplies and the graph responds to that automatically Interactive Excel Graph

This is pretty fun. This tabs works like filters and you can filter out anything you want right there in the graph and there’s no need to come back to your data. So if you boss asks to show graph for a particular month and for a particular region and only sales number, you have a solution, your  interactive excel graph.

Hope this helps.

How To Fix Audible Charges with 888-283-5051 NJ USA and Get Your Refund

How To Fix Audible Charges with 888-283-5051 NJ USA and Get Your Refund I regularly buy Kindle books from Amazon.com. But I never bought Audio books as they are kind of over priced. However, few days back, I was simply browsing through audible site and looking for types of books they are offering. I went there while exploring panachedesai.com/book. I didn’t click anything apart from landing page of the book and definitely didn’t hit the purchase button. But next day when I checked my bank statement in my mobile. I found $19.27 deducted along with $0.58 international fee. First I thought, may be I accidentally clicked purchased button. But later when I checked my Amazon account purchase history (as Audible is part of Amazon) I didn’t find any new audio book. I thought it was either some kind of BUG or some kind of SCAM.

How I fixed it?

I went to their site and wrote them a simple request message explaining about my situation. And they agreed to refund back.

Hi,

I recently browsed through audible website for Audio books. But I never purchased or subscribed anything.

But it has charged me $19.27 and $0.58 from my bank account on Wed 20 Apr 2016. My bank account is showing 888-283-5051 NJ USA as reference

Please help me to refund my money urgently.

Kind regards,
Rajesh

They replied as follows –

Hello Rajesh,

Thank you for contacting Audible!

My sincerest apologies for this upsetting situation. I have personally checked your account and I found out that the charges you have received, has come from an active Audible membership which started from a free trial membership.

We understand that this might have been accidental or, at the very least, you did not intend to continue with the regular paid membership. I am sorry our warnings were not clear or might have been bypassed.

Nonetheless, our job here at Customer Support is to remove such concerns from you or to make amends as best we can because, even if you do not want anything to do with Audible, we don’t want to leave you with a bad impression of us and we certainly do not want to be billing you for Audible products or services you do not want.

We have canceled your Audible membership and issued a refund back to your credit card. Depending on the bank, a refund can typically take about 7-10 days to appear in your account. Please rest assured that you will not receive any further charges from us since I have already cancelled your Audible Gold Monthly Membership.

Thank you again for contacting Audible. If you would like additional assistance, please do not hesitate to contact us. We are here to help 24 hours a day, 7 days a week.

Sincerely,

 

Image Credits: investopedia.com

How to clear all chats on whatsapp without clearing the special ones?

On Whatsapp if you clear chat, everything gets cleared and sometime you have to do it in order to save phone memory. But sometimes you also want to keep some special chats but clear other. Thanks to new update on Whatsapp, its possible now. If you don’t have an updated Whatsapp, go to playstore and update.  The process of clearing chat but keeping special ones is mentioned below

  1. Go to the chat you want to keep
  2. Touch and hold on the chat
  3. Keep touching as many as you want on that specific group or personal chat
  4. On the top you should see a starred sign, touch on that

whatsapp-clar-chat-keeping-specific-chat

5. If you want to clear chat for your entire whatsapp, follow the same process for all groups and individual chats. If you want to clear chat only for this group/person you are done.

6. Now there are two ways of clearing chats depending you want to clear your entire whatsapp or just for some specific groups or individual chats.

For entire whatsapp clearance

  1. Go to settings>Chats>Chat History>Clear all chats
  2. Check Keep starred messages and clear

whatsapp-clar-chat-keeping-specific-chat

 

For specific groups, individual chats

  1. Go to that specific chat
  2. Now touch on that 3 vertical dots on the top right corner
  3. Touch on more
  4. Clear Chat
  5. Check Keep starred messages and clear
  6. Follow the same process for other groups/individuals

 

How to read Whatsapp messages without showing Whatsapp blue tick

Whatsapp blue tick is a heck of a problem, right? Have you ever received a message on Whatsapp which you wanted to read so much but did not want to let the person know that you had read it. Have you ever cursed the Whatsapp blue tick ? So I have been researching about it lately for some reason and the most common solution I came across was Airplane mode or Flight mode. But I checked it by myself and to my surprise it doesn’t work at all. Even if you watch the message in Airplane mode and close Whatsapp completely before turning off Airplane mode, as soon as you connect to Internet, the sender sees the Whatsapp blue tick, hence not useful. So here’s my solution

  1. Go to Playstore and update your Whatsapp
  2. Now come back to Whatsapp and go to settings>Account>Privacy
  3. Uncheck the “Read receipts” option and you are all done.

whatsapp-blue-tick
Hide Whatsapp Blue Tick

 

Now even if you read any messages on Whatsapp , your sender won’t see any Whatsapp blue tick. The problem with this process is, its a two way process. So you will also not be allowed to see the blue tick. If that is not the solution you want, here’s one more solution for you. With this solution you will be able to see the Whatsapp message without showing the blue tick to your sender but you can see the blue tick as soon as someone reads your whatsapp message.

Just simply download another app from PlayStore, there are many apps , some of them are

  1. Shh, No Last seen or Read
  2. Blue tick, last seen hider

So just download any of this app, activate and follow instruction and you are all set.

Keep Whatsapping, secretly.